Using sp_executesql with parameters

The code I will share today is a cool trick when using sp_executesql. In some case, when you want to retreive only one record from a db, it could be interesting to have the result (the fields) or a part (some fields) of the result directly as variables instead of using another table to store the result and then re query to retreive the fields you want.

Imagine you have a Table called myContacts that have 6 columns:

id|Field1|Field2|Field3|Field4|Field5

And you want to query this table to retreive the record where id=4 and directly set some fields as variables, so you will be able to use them right after in you code.

Here is the trick :

declare
--The variables that will house the fields
@myField1 int,
@myField3 nvarchar(max),
@myField5 nvarchar(max),

--query variables
@sql nvarchar(max),
@ParamDef nvarchar(max)

--The query
select @sql = 'select @myField1=Field1, @myField3=Field3, @myField5=Field5 FROM myContacts WHERE id = 4'

--The parameters
select @ParamDef = '@myField1 int OUTPUT, @myField3 nvarchar(max) OUTPUT, @myField5 nvarchar(max) OUTPUT'

--Execute de sql statement
exec sp_executesql @sql, @paramDef, @myField1 OUTPUT, @myField3 OUTPUT, @myField5 OUTPUT

Print 'Field #1 = '+ convert(nvarchar,@myField1)+ ' Fields 3 & 5 : '+@myField3+',' +@myField5

OUTPUT : Field #1 = 1 Fields 3 & 5 : This is the Content 3, And this is Content 5

That’s an easy way to initiate variable on fly with from a sp_executesql result.

Ref. Link:
http://proofofconcepts.wordpress.com/2008/06/18/t-sql-multi-output-when-using-sp_executesql/#