We all rely on writing dynamic SQL in ColdFusion, but what if you need to write dynamic SQL on the server, perhaps in a stored procedure? While I'd not recommend overusing this technique (there are performance penalties to this one), if you need server-side dynamic SQL, the EXEC() function can help:
DECLARE @sql VARCHAR(100)
DECLARE @id INT
SET @id = 1234
SET @sql = 'SELECT * FROM MyTable'
IF @id ''
SET @sql = @sql + ' WHERE id = ' + @id
EXEC(@sql)
It gets a little trickier if the WHERE clause needs to compare string values, then you need quotes around the value, and those quotes need to be escaped:
DECLARE @sql VARCHAR(100)
DECLARE @id VARCHAR(100)
SET @id = 'A1234'
SET @sql = 'SELECT * FROM MyTable'
IF @id ''
SET @sql = @sql + ' WHERE id = ''' + @id + ''''
EXEC(@sql)
Dynamic SQL In SQL Server
·
7 Comments
B
Brian and Joe, I agree. As I said, there is a real performance hit, and it's a last resort option. But, as it so happens, today I needed it - I have a realy complex query with even more complex WHERE clauses. I am working on a better solution, but for now (the hack to get it working until I figure it all out) this was the quick and dirty workable option.
--- Ben
J
I'd pretty much only recommend doing this if a) there's no other choice, and b) the only values you're using in the dynamic statement are lookup up from data already in the DB, and don't consist of any user input. It's possible (though it takes a circumstance like bad use of preserveSingleQuotes()) to expose SQL injection points with this.
One place I have used this with good results is when the statement evaluated is name of another precompiled element, like dynamically choosing the name of a sproc to be invoked.
B
My understanding has always been that if you do this, the execution plan must be recompiled every time even though you are using a stored procedure, thus negating any performance benefit of using a sproc...plus (as you note) I have found a pretty decent performance hit when using this on anything more than simple statements.
D
As with anything, there are exceptions to every rule. I use a stored proc that dynamically generates SQL statements I use for pagination.
http://blog.pengoworks.com/blogger/index.cfm?action=blog:546
This allows me to pull out just specific "rows" from the query set. Since it uses the executesql stored proc, the query plan is cached and re-used. Performance of this stored proc is very good--better than any other SQL-based pagination solution I've found.
A
Amy Adler
Erland Sommarskog has an article titled "<a href="http://www.sommarskog.se/dyn-search.html">Dynamic Search Conditions in T-SQL</a>" that I found very helpful when I needed to write some dynamic SQL. He explains the different methods for writing dynamic SQL (EXEC() is not the only way, as I'm sure you know) and the tradeoffs of using them. Erland says that EXEC() is generally inferior to sp_executesql.
T
Tero Pikala
In Oracle same functionality is named EXECUTE_IMMEDIATE, obviously with same precautions as with SQL Server.
Tero
J
Hey Ben,
I've been there :) I just wanted to make sure readers saw all side of it.
Is the query problem something you can share? Maybe we can all work on a different sol'n.