Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1002
  • Last Modified:

Capture the result from dynamic sql statement

I have the following part of a script

@String is my table name
What im trying to do is capture the count value so that i can then update another table
What it does is retruns on the screen the value 91896 but the print @retVal remains empty.

      SELECT @SQL = N'SELECT COUNT(*) FROM '
      SELECT @SQL = @SQL + @STRING
      set @retType = N'@cnt varchar(20) OUTPUT'

      exec sp_executesql @sql, @retType, @retVal OUTPUT

      print 'ret val ' + convert(nvarchar(100),@retVal)
      

Any suggestions.
0
TRACEYMARY
Asked:
TRACEYMARY
  • 3
  • 3
  • 2
1 Solution
 
raopsnCommented:
0
 
raopsnCommented:
Try this;

     SELECT @SQL = N'SELECT @cnt = COUNT(*) FROM '
     SELECT @SQL = @SQL + @STRING
     set @retType = N'@cnt varchar(20) OUTPUT'

     exec sp_executesql @sql, @retType, @cnt = @retVal OUTPUT

     print 'ret val ' + convert(nvarchar(100),@retVal)
 
0
 
TRACEYMARYAuthor Commented:
Your a star....................thanks very much been on this all day long i could not work it out....

Thanks very much really appreciated have a great day.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
Anthony PerkinsCommented:
>>Thanks very much really appreciated have a great day.<<
Great!  Now please close the question.
0
 
Anthony PerkinsCommented:
Thank you for the points, but I believe you have mistaken me for raopsn, please see here from the EE Help:

I accepted the wrong answer. Now what?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi17
0
 
TRACEYMARYAuthor Commented:
so i did sorry .............i get it changed
0
 
TRACEYMARYAuthor Commented:
Thanks acperkins...........for be honest i did click wrong answer.
0
 
raopsnCommented:
Thanks Guys
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now