Dynamic SQL

Question

Post reply

Dynamic SQL

Beginner2012

SSC-Addicted

Points: 427
More actions
June 11, 2012 at 6:55 am

#262258

Hello,
Upon executing the following code I get this error:
-------------------------------------------------
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@MtCount".
-------------------------------------------------
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)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dave Brooking Ten Centuries Points: 1373 More actions · Answer 1

You have declared a variable called @MyCount, but your Dynamic SQL is using a variable called @MtCount.

Dave

Beginner2012 SSC-Addicted Points: 427 More actions · Answer 2

Sorry..I have updated the code..But still the same error message..

Msg 137, Level 15, State 1, Line 1

Must declare the scalar variable "@MyCount".

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

Beginner2012 (6/11/2012)
Hello,
Upon executing the following code I get this error:
-------------------------------------------------
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@MtCount".
-------------------------------------------------
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 @MtCount =COUNT(*) FROM ' + @MyTable
EXECUTE (@SqlCmd)

Three problems with your code: -

1. You've typo'd the name of the variable.

2. The variable is declared in a different context to where you've attempted to assign it.

3. Please look into SQL injection, what you've posted is almost a "how-to" for writing vulnerable code.

Try something like this: -

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

SET @MyTable = 'test'

SET @SqlCmd = 'SELECT @MtCountInner = COUNT(*) FROM ' + QUOTENAME(@MyTable);

EXECUTE sp_executesql @SqlCmd, N'@MtCountInner INT OUTPUT', @MtCountInner = @MyCount OUTPUT;

SELECT @MyCount;

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Dwayne Dibley SSCrazy Points: 2350 More actions · Answer 4

The variable @MyCount is in a different scope to teh dynamic SQL that you have declared.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

Dwayne Dibley (6/11/2012)
The variable @MyCount is in a different scope to teh dynamic SQL that you have declared.

Cadavre's call using sp_ExecuteSQL will take care of that particular nuance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)