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)
Leave a Reply