return variables from dynamic sql – sp_executesql

  • Published on Nov 1 2006
  • # 5,700
  • T-SQL

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.

One thought on “return variables from dynamic sql – sp_executesql

  1. ashok says:

    SET @vquery =’SELECT ‘+@keyparam+’ FROM [192.168.2.69].production.DBO.’+@flag_objects+’ WHERE env_archive=1′;

    SET @logTxt=’echo vquery –‘+@vquery+’ >>C:\ArchivalLogs\MARK_Archive_’+CONVERT(VARCHAR(10), GETDATE(), 120) +’.txt’
    EXEC master..xp_cmdshell @logTxt,no_output

    SET @vsql = ‘set @cursor = cursor for ‘ + @vquery + ‘; open @cursor’

    exec sys.sp_executesql
    @vsql
    ,N’@cursor cursor output’
    ,@C_MARK_RECORDS output

    I am using like above query but it is not giving the result. Any help Appreciated.

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>