Must declare the scalar variable sql ошибка - Исправление ошибок и поиск оптимальных решений проблем

This article lists out the extensive list of scenarios in which we get the following error message and how to resolve it.

Error Message:

Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable “%.*ls”.

Root Cause:

This error occurs if we are trying to use an undeclared variable

Below are the couple of scenarios in which we come across this error and how to resolve it.

Scenario 1: Trying to use an undeclared variable

Try executing the below statement

PRINT @AuthorName

RESULT:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable “@AuthorName”.

Reason for this error: In the above example, the variable @AuthorName is used in the PRINT statement without declaring it, which is not allowed by Sql Server.

Solution:Declare the @AuthorName variable before using it in the PRINT statement as below:

DECLARE @AuthorName VARCHAR(100) = 'Basavaraj Biradar'
PRINT @AuthorName

RESULT:

Scenario 2: Trying to use a local declared variable after batch separator GO statement

Try executing the below statement

DECLARE @AuthorName VARCHAR(100) = 'Basavaraj Biradar'
PRINT @AuthorName
GO
PRINT @AuthorName

RESULT:

Basavaraj Biradar
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable “@AuthorName”.

Reason for this error: In the above example, the variable @AuthorName is used in the PRINT statement after the batch separator GO Statement. Basically the scope of the local variables is within the batch in which it is declared.

Solution:Re-declare the @AuthorName variable before using it in the PRINT statement after the GO statement as below:

DECLARE @AuthorName VARCHAR(100) = 'Basavaraj Biradar'
PRINT @AuthorName
GO
DECLARE @AuthorName VARCHAR(100) = 'Basava'
PRINT @AuthorName

RESULT:

Scenario 3: Using local declared variable in the dynamic sql statement executed by the EXECUTE statement

Try executing the below statement

DECLARE @AuthorName VARCHAR(100) = 'Basavaraj Biradar'
EXECUTE ('PRINT @AuthorName')

RESULT:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable “@AuthorName”.

Reason for this error: In the above example, the variable @AuthorName is used in the statement executed by EXECUTE statement. EXECUTE statement doesn’t have the visibility of the variables declared outside of it.

Solution: We can rewrite the above statements as below to resolve this issue:

DECLARE @AuthorName VARCHAR(100) = 'Basavaraj Biradar'
EXECUTE ('PRINT ''' + @AuthorName + '''' )

RESULT:

Alternative solution: One more alternative solution for the above problem, is to use the SP_EXECUTESQL statement which allows parameterized statement as below:

DECLARE @AuthorName VARCHAR(100) = 'Basavaraj Biradar'
EXECUTE SP_EXECUTESQL N'PRINT @AuthorName',
           N'@AuthorName VARCHAR(100)',@AuthorName

RESULT:

Let me know if you have encountered this error in any other scenario.

Источник

Remove From My Forums

Question

Why does the code block below tell me that I must declare the scalar variable "@AddWhere"?



  


                       
  



DECLARE @SQL NVARCHAR (MAX)   
SET @SQL = ' Declare @DateFrom INT
set @DateFrom = 20120409
Declare @DateTo INT
set @DateTo = 20120411
DECLARE @StoreNo NVARCHAR(5)
SET @StoreNo = ''00013''
Declare @DealerID NVARCHAR(12)
Set @DealerID = ''zxcvbn'' 
Declare @AddWhere NVARCHAR(MAX)


IF @StoreNo = ''
	BEGIN
		SET @AddWhere = ''''

    END 
    
    ELSE 
		BEGIN
					SET @AddWhere = ''AND (c.Serial_Number IN
                          (SELECT     MAX(Serial_Number) AS Serial_Number
                            FROM          (SELECT     Serial_Number, SUBSTRING(Customer_ID, 3, 5) AS Customer_ID
                                                    FROM          dbo.Customers AS c
                                                    WHERE      (Class_Code = N''XYZ'')) AS customer
                            WHERE      (Customer_ID = '' +@storeno+'')))
		END



SELECT     TOP (100) PERCENT CASE dt.Dealer_ID WHEN ''bnmkl'' THEN SUBSTRING(dt.DEALER_ID, 4, 2) ELSE SUBSTRING(dt.DEALER_ID, 5, 1) END AS DIV, 
                      SUBSTRING(dt.Dealer_ID, 7, 2) AS REG, SUBSTRING(dt.Dealer_ID, 10, 3) AS DIST, SUBSTRING(c.Customer_ID, 3, 5) AS StoreNo, c.PostCode, c.Time_Zone, 
                      d.Day_Text, d.Date_Text, t.Time_Text_12_Hour, a.Event_Name, a.Area, a.User_Name, a.User_Number
FROM         dbo.CustomerActivity AS a INNER JOIN
                      dbo.Customers AS c ON a.Customer_Key = c.Customer_Key INNER JOIN
                          (SELECT     Dealer_Key, Parent_Dealer_Key, Dealer_ID, Parent_Dealer_ID, [Level], Has_Subdealers
                            FROM          dbo.DealerTree(@DealerID, 1, 0) AS DealerTree_1) AS dt ON c.Dealer_ID = dt.Dealer_ID INNER JOIN
                      dbo.Dates AS d ON a.Event_Date_Key = d.Date_Key INNER JOIN
                      dbo.Times AS t ON a.Event_Time_Key = t.Time_Key
WHERE     (a.Open_Close_Signal = 1) AND (a.Event_Name NOT IN (''LATE-TO-CLOSE'', ''CANCEL'', ''LATE-TO-OPEN'')) AND (d.Date_Key BETWEEN @DateFrom AND @DateTo) AND 
                      (c.Class_Code = ''XYZ'') ' + @AddWhere+'
                      
ORDER BY DIV, REG, DIST, c.Customer_ID, t.Time'

--PRINT @SQL

execute sp_executesql @SQL

Lee Markum

Edited by

Thursday, August 30, 2012 8:27 PM

Answers

Declare the parameters outside

DECLARE
@SQL
NVARCHAR (MAX)

Declare @AddWhere NVARCHAR(MAX)

—

Your query should be something like this : not 100% if it works but try :

Convert the date formats accordingly as per your req:

DECLARE @SQL NVARCHAR (MAX)
Declare @DateFrom date
set @DateFrom = ‘20120409’
Declare @DateTo date
set @DateTo = ‘20120411’
DECLARE @StoreNo NVARCHAR(5)
SET @StoreNo = ‘00013’
Declare @DealerID NVARCHAR(12)
Set @DealerID = ‘zxcvbn’
Declare @AddWhere NVARCHAR(MAX)

SET @SQL = ‘
IF @StoreNo = »
BEGIN
SET @AddWhere = »»

END


ELSE
BEGIN
SET @AddWhere = »AND (c.Serial_Number IN
(SELECT MAX(Serial_Number) AS Serial_Number
FROM (SELECT Serial_Number, SUBSTRING(Customer_ID, 3, 5) AS Customer_ID
FROM dbo.Customers AS c
WHERE (Class_Code = N»XYZ»)) AS customer
WHERE (Customer_ID = ‘+@storeno+’)))
END

SELECT TOP (100) PERCENT CASE dt.Dealer_ID WHEN »bnmkl» THEN SUBSTRING(dt.DEALER_ID, 4, 2) ELSE SUBSTRING(dt.DEALER_ID, 5, 1) END AS DIV,
SUBSTRING(dt.Dealer_ID, 7, 2) AS REG, SUBSTRING(dt.Dealer_ID, 10, 3) AS DIST, SUBSTRING(c.Customer_ID, 3, 5) AS StoreNo, c.PostCode, c.Time_Zone,
d.Day_Text, d.Date_Text, t.Time_Text_12_Hour, a.Event_Name, a.Area, a.User_Name, a.User_Number
FROM dbo.CustomerActivity AS a INNER JOIN
dbo.Customers AS c ON a.Customer_Key = c.Customer_Key INNER JOIN
(SELECT Dealer_Key, Parent_Dealer_Key, Dealer_ID, Parent_Dealer_ID, [Level], Has_Subdealers
FROM dbo.DealerTree(‘+@DealerID+’, 1, 0) AS DealerTree_1) AS dt ON c.Dealer_ID = dt.Dealer_ID INNER JOIN
dbo.Dates AS d ON a.Event_Date_Key = d.Date_Key INNER JOIN
dbo.Times AS t ON a.Event_Time_Key = t.Time_Key
WHERE (a.Open_Close_Signal = 1) AND (a.Event_Name NOT IN (»LATE-TO-CLOSE», »CANCEL», »LATE-TO-OPEN»)) AND (d.Date_Key BETWEEN’+ @DateFrom + ‘AND’ +@DateTo +’) AND
(c.Class_Code = »XYZ») ‘ + @AddWhere+’


ORDER BY DIV, REG, DIST, c.Customer_ID, t.Time’
- Edited by
  JR1811
  Thursday, August 30, 2012 9:08 PM
- Proposed as answer by
  Naomi N
  Friday, August 31, 2012 8:14 PM
- Marked as answer by
  Kalman Toth
  Tuesday, September 4, 2012 6:38 PM

Источник

Home > SQL Server Error Messages > Msg 137 — Must declare the scalar variable «<Variable Name>».

SQL Server Error Messages — Msg 137 — Must declare the scalar variable «<Variable Name>».

SQL Server Error Messages — Msg 137

Error Message

Server: Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "<Variable Name>".

Causes

As the error message suggests, a local variable is being used within a script, stored procedure or function that is not declared using the DECLARE statement.

To illustrate, the simplest way to generate this error is as follows:

SET @FirstName = 'Mickey'

Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@FirstName".

SELECT @HighestScore

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@HighestScore".

A not-so-obvious way of getting this error message is as follows:

DECLARE @DateFormat INT

SET @DateFormat = 0
WHILE @DateFormat < 15
BEGIN
    PRINT CONVERT(VARCHAR(30), GETDATE(), @DateFormat)
    SET @DateFormat = @DateFormat + 1
END

GO

SET @DateFormat = 100
WHILE @DateFormat < 115
BEGIN
    PRINT CONVERT(VARCHAR(30), GETDATE(), @DateFormat)
    SET @DateFormat = @DateFormat + 1
END
GO
Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@DateFormat".

This script tries to print the current date into the different date formats between date formats 0 to 14 followed by date formats 100 to 114. Although the @DateFormat local variable is declared at the beginning of the script, there is a GO command just before the group of statements that prints the current date into the different date formats from 100 to 114. The GO command signals the end of a batch of Transact-SQL statements. A local variable is only valid within the body of a batch or procedure. Since there is a GO command, the @DateFormat local variable will not exist anymore on the second batch of commands.

Yet another way of getting this error is when using a local variable declared outside a dynamic SQL statement executed using the EXECUTE statement. To illustrate:

DECLARE @ColumnName		VARCHAR(100)
SET @ColumnName = 'FirstName'

EXECUTE ('SELECT [CustomerID], @ColumnName FROM [dbo].[Customers]')
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@ColumnName".

This script tries to return the row values for a given column as defined in the @ColumnName local variable from a given table.

Solution / Workaround:

As the message suggests, this error can easily be avoided by making sure that a local variable is defined first using the DECLARE statement before being used.

In the first case described above, simply declare the local variables just before setting its value or returning its value as part of a SELECT statement:

DECLARE @FirstName	VARCHAR(50)
SET @FirstName = 'Mickey'
GO

DECLARE @HighestScore	INT
SELECT @HighestScore
GO

As for the second scenario, there are 2 ways of avoiding the error. The first option is to remove the GO command between the 2 sets of scripts so that the local variable @DateFormat is valid and accessible on both scripts:

DECLARE @DateFormat INT

SET @DateFormat = 0
WHILE @DateFormat < 15
BEGIN
    PRINT CONVERT(VARCHAR(30), GETDATE(), @DateFormat)
    SET @DateFormat = @DateFormat + 1
END

SET @DateFormat = 100
WHILE @DateFormat < 115
BEGIN
    PRINT CONVERT(VARCHAR(30), GETDATE(), @DateFormat)
    SET @DateFormat = @DateFormat + 1
END
GO

The second option is to define the local variable @DateFormat again just after the GO command and just before it gets initialized and used:

DECLARE @DateFormat INT

SET @DateFormat = 0
WHILE @DateFormat < 15
BEGIN
    PRINT CONVERT(VARCHAR(30), GETDATE(), @DateFormat)
    SET @DateFormat = @DateFormat + 1
END
GO

DECLARE @DateFormat INT

SET @DateFormat = 100
WHILE @DateFormat < 115
BEGIN
    PRINT CONVERT(VARCHAR(30), GETDATE(), @DateFormat)
    SET @DateFormat = @DateFormat + 1
END
GO

And lastly for the third scenario, the query needs to be re-written such that the value of the local variable is used in the dynamic statement instead of the local variable.

DECLARE @ColumnName		VARCHAR(100)
SET @ColumnName = 'FirstName'

EXECUTE ('SELECT ' + @ColumnName + ' FROM [dbo].[Customers]')

Frequently Asked
Questions — SQL Server Error Messages
Tips & Tricks — SQL Server Error Messages 1 to 500

Источник

Содержание

SqlHints.com
By Basavaraj Biradar
Must declare the scalar variable – Error Message 137
Error Message:
Root Cause:
Scenario 1: Trying to use an undeclared variable
Scenario 2: Trying to use a local declared variable after batch separator GO statement
Scenario 3: Using local declared variable in the dynamic sql statement executed by the EXECUTE statement
Sql must declare the scalar variable error in
All replies
Sql must declare the scalar variable error in
Sql must declare the scalar variable error in
Answered by:
Question

SqlHints.com

By Basavaraj Biradar

Must declare the scalar variable – Error Message 137

This article lists out the extensive list of scenarios in which we get the following error message and how to resolve it.

Error Message:

Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable “%.*ls”.

Root Cause:

This error occurs if we are trying to use an undeclared variable

Below are the couple of scenarios in which we come across this error and how to resolve it.

Scenario 1: Trying to use an undeclared variable

Try executing the below statement

RESULT:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable “@AuthorName”.

Reason for this error: In the above example, the variable @AuthorName is used in the PRINT statement without declaring it, which is not allowed by Sql Server.

Solution:Declare the @AuthorName variable before using it in the PRINT statement as below:

RESULT:

Scenario 2: Trying to use a local declared variable after batch separator GO statement

Try executing the below statement

RESULT:

Basavaraj Biradar
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable “@AuthorName”.

Solution:Re-declare the @AuthorName variable before using it in the PRINT statement after the GO statement as below:

RESULT:

Scenario 3: Using local declared variable in the dynamic sql statement executed by the EXECUTE statement

Try executing the below statement

RESULT:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable “@AuthorName”.

Solution: We can rewrite the above statements as below to resolve this issue:

RESULT:

Alternative solution: One more alternative solution for the above problem, is to use the SP_EXECUTESQL statement which allows parameterized statement as below:

RESULT:

Let me know if you have encountered this error in any other scenario.

Источник

Sql must declare the scalar variable error in

Declare the parameters outside

DECLARE @ SQL NVARCHAR ( MAX )

Declare @AddWhere NVARCHAR(MAX)

Your query should be something like this : not 100% if it works but try :

Convert the date formats accordingly as per your req:

DECLARE @SQL NVARCHAR (MAX)
Declare @DateFrom date
set @DateFrom = ‘20120409’
Declare @DateTo date
set @DateTo = ‘20120411’
DECLARE @StoreNo NVARCHAR(5)
SET @StoreNo = ‘00013’
Declare @DealerID NVARCHAR(12)
Set @DealerID = ‘zxcvbn’
Declare @AddWhere NVARCHAR(MAX)

SET @SQL = ‘
IF @StoreNo = »
BEGIN
SET @AddWhere = »»

ELSE
BEGIN
SET @AddWhere = »AND (c.Serial_Number IN
(SELECT MAX(Serial_Number) AS Serial_Number
FROM (SELECT Serial_Number, SUBSTRING(Customer_ID, 3, 5) AS Customer_ID
FROM dbo.Customers AS c
WHERE (Class_Code = N»XYZ»)) AS customer
WHERE (Customer_ID = ‘+@storeno+’)))
END

SELECT TOP (100) PERCENT CASE dt.Dealer_ID WHEN »bnmkl» THEN SUBSTRING(dt.DEALER_ID, 4, 2) ELSE SUBSTRING(dt.DEALER_ID, 5, 1) END AS DIV,
SUBSTRING(dt.Dealer_ID, 7, 2) AS REG, SUBSTRING(dt.Dealer_ID, 10, 3) AS DIST, SUBSTRING(c.Customer_ID, 3, 5) AS StoreNo, c.PostCode, c.Time_Zone,
d.Day_Text, d.Date_Text, t.Time_Text_12_Hour, a.Event_Name, a.Area, a.User_Name, a.User_Number
FROM dbo.CustomerActivity AS a INNER JOIN
dbo.Customers AS c ON a.Customer_Key = c.Customer_Key INNER JOIN
(SELECT Dealer_Key, Parent_Dealer_Key, Dealer_ID, Parent_Dealer_ID, [Level], Has_Subdealers
FROM dbo.DealerTree(‘+@DealerID+’, 1, 0) AS DealerTree_1) AS dt ON c.Dealer_ID = dt.Dealer_ID INNER JOIN
dbo.Dates AS d ON a.Event_Date_Key = d.Date_Key INNER JOIN
dbo.Times AS t ON a.Event_Time_Key = t.Time_Key
WHERE (a.Open_Close_Signal = 1) AND (a.Event_Name NOT IN (»LATE-TO-CLOSE», »CANCEL», »LATE-TO-OPEN»)) AND (d.Date_Key BETWEEN’+ @DateFrom + ‘AND’ +@DateTo +’) AND
(c.Class_Code = »XYZ») ‘ + @AddWhere+’

ORDER BY DIV, REG, DIST, c.Customer_ID, t.Time’

It is «complaining» about the @AddWhere at the very end, where it says «(c.Class_Code = »XYZ»)’ + @AddWhere». You should probably replace «‘ + @AddWhere + ‘» with » + @AddWhere + «.

Declare the parameters outside

DECLARE @ SQL NVARCHAR ( MAX )

Declare @AddWhere NVARCHAR(MAX)

Your query should be something like this : not 100% if it works but try :

Convert the date formats accordingly as per your req:

SET @SQL = ‘
IF @StoreNo = »
BEGIN
SET @AddWhere = »»

ORDER BY DIV, REG, DIST, c.Customer_ID, t.Time’

Thanks, Sachin Surve

Good SQL Programers hate procedural code that cannot help the optimizer. Good SQL programmers hate local variables because they require disk access and force procedural coding. Good SQL programmers hate control flow with the if-then-else and loops. We never use dynamic SQL more than a few rare times in our entire career. We also follow ISO-11179 naming rules.

You are doing a bad job of writing bad SQL . Did you know that we have a DATE data type? Yet you use INTEGER in the generated code. Why are the store numbers varying strings? Most standard encoding are fixed length strings.

There is no such thing AS a “code_class”; those are what ISO calls attribute properties, so we need a “ _class” or a “ _code and never this weird pile of adjectives looking for a noun. I will guess “customer_class” was intended. And obviously “customer_id” is wrong.

We never include a data type in a name, so things like “date_text” which implies you formatting data in the back enD. The basic principle of a tiered architecture is that display is done in the front end and never in the back end. This is a more basic programming principle than just SQL and RDBMS.

We do not use flags in SQL; that was assembly language and not SQL.

We have temporal data types so those date and time tables are a needless nightmare to ruin performance, portability and any hope of maintainable code.

It looks like Dealers are in an adjacency list model for a hierarchy; the Nested sets model is better.

Looking at the rest of the code, you seem to have no idea what First Normal Form (1NF) and have packed many data elements into columns that you have pull out with SUBSTRING() functions.

Since you did not bother with DDL, my guess is that this ought to be simple stored procedure, vaguely like this skeleton once you correct the schema:

CREATE PROCEDURE Report_Store_Event
(@in_something_start_date DATE,
@in_something_end_date DATE,
@in_store_nbr CHAR(5),
@in_dealer_id CHAR(12))

SELECT divison_nbr, region_nbr, district_nbr, store_nbr,
C.postal_code, C.time_zone,
A.event_name, A.area_code, A.user_name, A.user_number
FROM Customer_Activities AS A,
Customers AS C,
Dealers AS D
WHERE A.store_operation_code
NOT IN (‘LATE-TO-CLOSE’, ‘CANCEL’, ‘LATE-TO-OPEN’))
AND event_date
BETWEEN @in_something_start_date AND @in_something_end_date)
AND C.customer_class = ‘XYZ’
AND A.customer_id = C.customer_id
AND D.dealer_id = @in_dealer_id
AND A.store_nbr = @in_store_nbr;

This can run 2 to 3 orders of magnitude better than what you have now.

—CELKO— Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Источник

Sql must declare the scalar variable error in

Where did you run this query to get this error message?

Just run this posted query in SSMS to see whether it works.

I use navicat, and if i remove declare variable and replace all variable @startdate & @enddate with date value it works fine, but how to make navicat works with that variable?

I find that Navicat is not a product of Microsoft and it is a third party service which is not supported by Microsoft.

So we are not aware about Navicat. How it works and what’s its functionality.

I suggest you to make a test with SSMS and check whether you get same error or not.

If you get any error with SSMS then we can try to provide you further suggestions to solve the issue.

If you need to work with Navicat then I suggest you to contact a support forum for Navicat can provide you a suitable suggestions that may help you to solve this issue.

Thanks for your understanding.

I find that Navicat is not a product of Microsoft and it is a third party service which is not supported by Microsoft.

So we are not aware about Navicat. How it works and what’s its functionality.

I suggest you to make a test with SSMS and check whether you get same error or not.

If you get any error with SSMS then we can try to provide you further suggestions to solve the issue.

If you need to work with Navicat then I suggest you to contact a support forum for Navicat can provide you a suitable suggestions that may help you to solve this issue.

Thanks for your understanding.

Thanks for your advice, appreciate it. I will update if there same error in SSMS

Источник

Sql must declare the scalar variable error in

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I am getting the error — Must Declare Scalar Variable @BackupFile when running this backup job. It’s not occurring on the backup statement, but the last statement that is doing the verify — «RESTORE VERIFYONLY FROM DISK = @BackupFile WITH . «

It’s like that statement is not seeing the @BackupFile variable declaration at the top.

declare @BackupFile VARCHAR(1000)
Select @BackupFile = ‘D:MSSQLDataMSSQL11.MSSQLSERVERMSSQLBackupSourceAnywhere_’ + CONVERT(nvarchar(30), GETDATE(), 110) + ‘.bak’
BACKUP DATABASE [SourceAnywhere] TO DISK = @BackupFile WITH NOFORMAT, INIT, NAME = N’SourceAnywhere-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N’SourceAnywhere’ and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N’SourceAnywhere’ )
if @backupSetId is null begin raiserror(N’Verify failed. Backup information for database »SourceAnywhere» not found.’, 16, 1) end
RESTORE VERIFYONLY FROM DISK = @BackupFile WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO

Источник

Remove From My Forums

Question
I am getting the error «Must declare the scalar variable «@Result»» when i execute the below query

declare @sql nvarchar(max),

@tablename varchar(200),
@Id int,
@Result int

set @tablename=’xyz’
set @id=1

SET @sql = ‘
SELECT @Result=COUNT( Id ) FROM ‘ + @TableName+ ‘ WHERE RunRegisterKey=’ +convert(nvarchar(100),@ID)

EXEC sys.sp_executesql @sql

Answers

I need to compare the @Result value with other then display it otherwise ignore like,

If(@result>0)

select @result

just add that in the end
```
declare @sql nvarchar(max),
@tablename varchar(200),
@Id int,
@Result int,
@Params nvarchar(100)

set @tablename='xyz'
set @id=1
SET @Params = N'@Result int OUTPUT'

SET @sql = N'
SELECT @Result=COUNT( Id ) FROM  ' + @TableName+ ' WHERE RunRegisterKey=' +convert(nvarchar(100),@ID)  

EXEC sys.sp_executesql @sql,@Params,@Result = @Result OUT

IF @Result > 0
SELECT @Result
```
Please Mark This As Answer if it helps to solve the issue Visakh —————————- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs
- Marked as answer by
  
  Monday, May 19, 2014 1:20 PM
In addition to Visakh’s solution: the reason you got the error message is because the piece of dynamic SQL is not part of the stored procedure, but constiutes a scope of its own. Thus variables declared in the surrounding procedure are not visible.

Also, you should the dynamic SQL this way:

SET @sql = N’
SELECT @Result=COUNT( Id ) FROM ‘ + quotename(@TableName) +
‘ WHERE RunRegisterKey=@ID’
SET @Params = N’@Result int OUTPUT, @ID int’
EXEC sys.sp_executesql @sql,@Params,@Result = @Result OUT, @ID = @ID

That is, use quotename for the table name, in case you have a table named
```
sys.objects; SHUTDOWN WITH NOWAIT; --
```
Furthermore, pass @ID as a parameter rather than concatenating it to the string. It is both easier and safer.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
- Proposed as answer by
  Naomi N
  Sunday, May 11, 2014 4:19 AM
- Marked as answer by
  Fanny Liu
  Monday, May 19, 2014 1:20 PM

Источник

Error Message:

Msg 137, Level 15, State 1, Line 1 Must declare the scalar variable “%.*ls”.

Root Cause:

Scenario 1: Trying to use an undeclared variable

Scenario 2: Trying to use a local declared variable after batch separator GO statement

Scenario 3: Using local declared variable in the dynamic sql statement executed by the EXECUTE statement

Question

Answers

SqlHints.com

By Basavaraj Biradar

Must declare the scalar variable – Error Message 137

Error Message:

Msg 137, Level 15, State 1, Line 1 Must declare the scalar variable “%.*ls”.

Root Cause:

Scenario 1: Trying to use an undeclared variable

Scenario 2: Trying to use a local declared variable after batch separator GO statement

Scenario 3: Using local declared variable in the dynamic sql statement executed by the EXECUTE statement

Sql must declare the scalar variable error in

Sql must declare the scalar variable error in

Sql must declare the scalar variable error in

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif" />

Answered by:

Question

Question

Answers

Читайте также:

Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable “%.*ls”.

Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable “%.*ls”.