T-SQL multi OUTPUT when using sp_executesql

One of my current professional dev project is to write an ETL from scratch using a lot of technologies. I had to play a lot with T-SQL (Transact-SQL) which was quite interesting to work with.

The code I will share today (not about RIA at all) 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       <id as a primary key

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.