SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Dynamic SQL		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Beginer2012
Starting Member

45 Posts
Posted - 06/11/2012 :  08:56:20  Show Profile  Reply with Quote
Hello,

Upon executing the following code I get this error:
-------------------------------------------------
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@MyCount".
-------------------------------------------------


I need to store the count in a variable dynamically to genrate complicated report.
Any sugestions ?

Thank you

DECLARE @MyCount INT
DECLARE @MyTable VARCHAR(10)
DECLARE @SqlCmd NVARCHAR(500)

SET @Mytable = 'test'

SET @SqlCmd = 'SELECT @MyCount =COUNT(*) FROM ' + @MyTable

EXECUTE (@SqlCmd)
Edited by - Beginer2012 on 06/11/2012 09:11:06

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 06/11/2012 :  09:22:42  Show Profile  Reply with Quote
You should use the sp_executesql interface to get data out from the dynamic SQL query. http://msdn.microsoft.com/en-us/library/ms188001.aspx

Your query would be something like this (untested):
DECLARE @MyCount INT
DECLARE @MyTable VARCHAR(10)
DECLARE @SqlCmd NVARCHAR(500)

SET @Mytable = 'test'

SET @SqlCmd = 'SELECT @TheCount =COUNT(*) FROM ' + @MyTable

EXEC sp_executesql  
	@stmt = @SqlCmd, 
	@params = N'@TheCount as int OUTPUT', 
	@TheCount = @MyCount OUTPUT;
select @MyCount;


Note, OUTPUT specified in two places. Also, I changed the variable name in the dynamic SQL to @TheCount just to show the distinction.

[Insert usual advice about avoiding dynamic sql if at all possible here]
Go to Top of Page

Beginer2012
Starting Member

45 Posts
Posted - 06/11/2012 :  09:25:18  Show Profile  Reply with Quote
Thank you very much for your help.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15559 Posts
Posted - 06/11/2012 :  12:19:21  Show Profile  Visit robvolk's Homepage  Reply with Quote
If all you need is a count of all rows in the table:
DECLARE @MyTable varchar(100), @MyCount bigint
SELECT @MyCount=rows FROM sys.partitions WHERE OBJECT_NAME(object_id)=@MyTable AND index_id<2
As sunita suggested, there's no dynamic SQL needed.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000