Returing values into tsql variables from a dynamic sql statement using sp_executesql stored procedure.

Scenario

The example below displays the number of objects per database. The following steps are involved.

• open a cursor for the select * from master.dbo.sysdatabases statement (cursor is a while loop),
• inside the cursor a dynamic sql statement is defined ,
• at last the stored procedure sp_executesql is used to retrieve the output of this defined sql statement into tsql variables .

sp_execute example to return values into variables

1> declare @s_dtb  sysname
2> declare @s_sql  nvarchar(2000)
3> declare @i_obj  int
4> declare c1     cursor
5> for
6> select name
7>   from master.dbo.sysdatabases
8>
9>
10> open c1
11> fetch c1 into @s_dtb
12>
13> while @@fetch_status >= 0
14> begin
15>
16>   -- construct sql statement
17>   set @s_sql = 'select @count = count(*)
18>                  from ' + QuoteName(@s_dtb) + '.dbo.sysobjects'
19>
20>   -- execute dynamic sql and map output variable @count to variable @i_obj
21>   exec sp_executesql
22>     @s_sql,
23>     N'@count int output',
24>     @count = @i_obj output
25>
26>   print @s_dtb + ' db has ' + cast( @i_obj as varchar) + ' objects'
27>
28>   fetch c1 into @s_dtb
29>
30> end
31>
32> close c1
33> deallocate c1
34>
35>
36> go
master db has 1324 objects
model db has 54 objects
msdb db has 454 objects
Northwind db has 146 objects
tempdb db has 54 objects
1>

Extremely handy I would say. – Whenever you want to perform an operation (execute tsql) on all your databases.