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.
LVL 7
TRACEYMARYAsked:
Who is Participating?
 
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
 
raopsnCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.