There are times when you just can’t avoid using dynamic SQL. Usually this is happens when you end up working with a poorly designed database backend. All developers have done it at some point of time and creation and execution of dynamic sql through business code is commonly seen. In TSQL, I’ve also seen many using the EXEC command to execute the dynamic sql. TOday, I found something I didn’t know about which is sp_executesql.
sp_executesql came in SQL server 7 . You can use it not only to execute dynamic sql but what makes it great is that you can pass to your dynamic sql, parameters and yes… a parameter may be an out parameter!
syntax: sp_executesql @stmt, @params, [@paramvalue1, @paramvalue2…]
Crude example:
DECLARE @TblName varchar(50)
DECLARE @ColValue int
set @ColValue=30
set @tblName='MyTable'
sp_executesql 'Select * from ' + @MyTable +' where somecol = @ColVal' , '@ColVal int', @ColValue
Crude example with an out parameter:
DECLARE @TblName varchar(50)
DECLARE @ColValue int
DECLARE @CountReturned int
set @ColValue=30
set @tblName='MyTable'
sp_executesql 'Select @Count = count (*) from ' + @MyTable +' where somecol = @ColVal' , '@ColVal int, @Count int out', @ColValue, @Count=@CountReturned output
Here is a great article on dynamic SQL :http://www.sommarskog.se/dynamic_sql.html
Subscribe