Must declare the scalar variable sql ошибка

@RowFrom int

@RowTo int

are both Global Input Params for the Stored Procedure, and since I am compiling the SQL query inside the Stored Procedure with T-SQL then using Exec(@sqlstatement) at the end of the stored procedure to show the result, it gives me this error when I try to use the @RowFrom or @RowTo inside the @sqlstatement variable that is executed.. it works fine otherwise.. please help.

"Must declare the scalar variable "@RowFrom"."

Also, I tried including the following in the @sqlstatement variable:

'Declare @Rt int'
'SET @Rt = ' + @RowTo

but @RowTo still doesn’t pass its value to @Rt and generates an error.

hofnarwillie

3,54310 gold badges47 silver badges73 bronze badges

asked Aug 24, 2011 at 20:39

You can’t concatenate an int to a string. Instead of:

SET @sql = N'DECLARE @Rt int; SET @Rt = ' + @RowTo;

You need:

SET @sql = N'DECLARE @Rt int; SET @Rt = ' + CONVERT(VARCHAR(12), @RowTo);

To help illustrate what’s happening here. Let’s say @RowTo = 5.

DECLARE @RowTo int;
SET @RowTo = 5;

DECLARE @sql nvarchar(max);
SET @sql = N'SELECT ' + CONVERT(varchar(12), @RowTo) + ' * 5';
EXEC sys.sp_executesql @sql;

In order to build that into a string (even if ultimately it will be a number), I need to convert it. But as you can see, the number is still treated as a number when it’s executed. The answer is 25, right?

In your case you can use proper parameterization rather than use concatenation which, if you get into that habit, you will expose yourself to SQL injection at some point (see this and this:

SET @sql = @sql + ' WHERE RowNum BETWEEN @RowFrom AND @RowTo;';

EXEC sys.sp_executesql @sql,
  N'@RowFrom int, @RowTo int',
  @RowFrom, @RowTo;

answered Aug 24, 2011 at 21:01

Aaron BertrandAaron Bertrand

268k36 gold badges457 silver badges485 bronze badges

You can also get this error message if a variable is declared before a GOand referenced after it.

See this question and this workaround.

answered Mar 25, 2019 at 22:11

Pierre CPierre C

2,42132 silver badges31 bronze badges

Just FYI, I know this is an old post, but depending on the database COLLATION settings you can get this error on a statement like this,

SET @sql = @Sql + ' WHERE RowNum BETWEEN @RowFrom AND @RowTo;';

if for example you typo the S in the

SET @sql = @***S***ql

sorry to spin off the answers already posted here, but this is an actual instance of the error reported.

Note also that the error will not display the capital S in the message, I am not sure why, but I think it is because the

Set @sql =

is on the left of the equal sign.

answered Apr 1, 2015 at 19:13

htm11hhtm11h

1,7198 gold badges46 silver badges103 bronze badges

Sometimes, if you have a ‘GO’ statement written after the usage of the variable, and if you try to use it after that, it throws such error. Try removing ‘GO’ statement if you have any.

answered May 24, 2021 at 6:12

This is most likely not an answer to the issue itself, but this question pops up as first result when searching for Sql declare scalar variable hence I want to share a possible solution to this error.

In my case this error was caused by the use of ; after a SQL statement. Just remove it and the error will be gone.

I guess the cause is the same as @IronSean already posted in a comment above:

it’s worth noting that using GO (or in this case 😉 causes a new branch where declared variables aren’t visible past the statement.

For example:

DECLARE @id int
SET @id = 78

SELECT * FROM MyTable WHERE Id = @var; <-- remove this character to avoid the error message
SELECT * FROM AnotherTable WHERE MyTableId = @var

answered Nov 5, 2020 at 16:25

ViRuSTriNiTyViRuSTriNiTy

4,9122 gold badges31 silver badges56 bronze badges

Just adding what fixed it for me, where misspelling is the suspect as per this MSDN blog…

When splitting SQL strings over multiple lines, check that that you are comma separating your SQL string from your parameters (and not trying to concatenate them!) and not missing any spaces at the end of each split line. Not rocket science but hope I save someone a headache.

For example:

db.TableName.SqlQuery(
    "SELECT Id, Timestamp, User " +
    "FROM dbo.TableName " +
    "WHERE Timestamp >= @from " +
    "AND Timestamp <= @till;" + [USE COMMA NOT CONCATENATE!]
    new SqlParameter("from", from),
    new SqlParameter("till", till)),
    .ToListAsync()
    .Result;

EBH

10.3k3 gold badges32 silver badges58 bronze badges

answered Jun 21, 2017 at 15:46

Tim TylerTim Tyler

2,1612 gold badges17 silver badges12 bronze badges

Case Sensitivity will cause this problem, too.

@MyVariable and @myvariable are the same variables in SQL Server Man. Studio and will work. However, these variables will result in a «Must declare the scalar variable «@MyVariable» in Visual Studio (C#) due to case-sensitivity differences.

answered Jun 9, 2016 at 11:20

Just an answer for future me (maybe it helps someone else too!). If you try to run something like this in the query editor:

USE [Dbo]
GO

DECLARE @RC int

EXECUTE @RC = [dbo].[SomeStoredProcedure] 
   2018
  ,0
  ,'arg3'
GO

SELECT month, SUM(weight) AS weight, SUM(amount) AS amount 
FROM SomeTable AS e 
WHERE year = @year AND type = 'M'

And you get the error:

Must declare the scalar variable «@year»

That’s because you are trying to run a bunch of code that includes BOTH the stored procedure execution AND the query below it (!). Just highlight the one you want to run or delete/comment out the one you are not interested in.

marc_s

721k173 gold badges1320 silver badges1442 bronze badges

answered Jul 21, 2019 at 18:05

saiyancodersaiyancoder

1,2551 gold badge13 silver badges20 bronze badges

If someone else comes across this question while no solution here made my sql file working, here’s what my mistake was:

I have been exporting the contents of my database via the ‘Generate Script’ command of Microsofts’ Server Management Studio and then doing some operations afterwards while inserting the generated data in another instance.

Due to the generated export, there have been a bunch of «GO» statements in the sql file.

What I didn’t know was that variables declared at the top of a file aren’t accessible as far as a GO statement is executed. Therefore I had to remove the GO statements in my sql file and the error «Must declare the scalar variable xy» was gone!

answered Oct 19, 2020 at 10:33

pburpbur

656 bronze badges

As stated in https://learn.microsoft.com/en-us/sql/t-sql/language-elements/sql-server-utilities-statements-go?view=sql-server-ver16 , the scope of a user-defined variable is batch dependent .

—This will produce the error

 GO   
    DECLARE @MyVariable int;
    SET @MyVariable = 1;
    GO --new batch of code
    SELECT @MyVariable--CAST(@MyVariable AS 
    int);
 GO

—This will not produce the error

 GO   
   DECLARE @MyVariable int;
   SET @MyVariable = 1;
   SELECT @MyVariable--CAST(@MyVariable AS int);
 GO

We get the same error when we try to pass a variable inside a dynamic SQL:

GO
DECLARE @ColumnName VARCHAR(100),
        @SQL NVARCHAR(MAX);
SET @ColumnName = 'FirstName';
EXECUTE ('SELECT [Title],@ColumnName FROM Person.Person');  
GO

—In the case above @ColumnName is nowhere to be found, therefore we can either do:

EXECUTE ('SELECT [Title],' +@ColumnName+ ' FROM Person.Person');

GO
DECLARE @ColumnName VARCHAR(100),
        @SQL NVARCHAR(MAX);
SET @ColumnName = 'FirstName';
SET @SQL = 'SELECT ' + @ColumnName + ' FROM Person.Person';
EXEC sys.sp_executesql @SQL  
GO

answered Sep 15, 2022 at 10:39

Give a ‘GO’ after the end statement and select all the statements then execute

answered Dec 29, 2021 at 15:23

Источник

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:
Error Message 128

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:
Error Message 137

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:
Must declare the scalar variable

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:
Must declare the scalar variable SP_EXECUTESQL

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

Источник

My syntax keeps giving me the below error, which is blowing my mind as I think (and please kindly correct me if I am incorrect), I have declared and set this variable above.

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable «@id».

Here is my syntax, and if I include a print @id statement the proper value will be output, however I still get the above error?!

Create Table #temphold
(
  dateadded datetime
  ,dateupdated datetime
  ,id varchar(100)
)

Declare @id varchar(100), @sql varchar(max)
Set @id = '12345'


set @sql = 'insert into   #temphold(dateadded,dateupdated,id) '
          +'select   getdate(),getdate(),COALESCE(@id,'''') '
PRINT @SQL
EXEC(@SQL)


Drop Table #temphold

asked Mar 24, 2016 at 12:41

Michael MormonMichael Mormon

4613 gold badges8 silver badges16 bronze badges

@id as part of the execution variable @sql is doing nothing. It is not tied to the declared and set variable unless you build the string around it, i.e. concatenate it to the string like this:

set @sql = 'insert into #temphold(dateadded,dateupdated,id) ' +'select getdate(),getdate(),COALESCE(' + @id + ','''') '

Notice the + either side of the @id variable.

At the end of the day, @sql is just a string until it’s executed using the EXEC() command. Simply treat it as such until it compiles as fully qualified T-SQL.

answered Mar 24, 2016 at 13:22

MolenpadMolenpad

1,7442 gold badges18 silver badges36 bronze badges

You can also pass @id in as a parameter to the dynamic query instead of putting it in as a literal. To do this you need to use the sp_executesql function instead of EXEC. IMO passing parameters into the statement helps make the generated statement a little more clear/readable and you don’t have to cast or convert certain data types into a nvarchar equivalent. Doing it this way also gives you a better chance of SQL Server generating a query plan that it could reuse.

Here is a post on Stack Overflow that provides some compare and contrasts. Stored procedure EXEC vs sp_executesql difference?

Here is Microsoft’s technet article on sp_executesql

Create Table #temphold
(
  dateadded datetime
  ,dateupdated datetime
  ,id varchar(100)
)

Declare @id varchar(100), @sql nvarchar(max)
Set @id = '12345'


set @sql = 'insert into   #temphold(dateadded,dateupdated,id) '
          +'select   getdate(),getdate(),COALESCE(@id,'''') '
PRINT @SQL;
execute sp_executesql @statement = @sql, @parameters = N'@id varchar(100)', @id = @id

Drop Table #temphold

answered Mar 24, 2016 at 18:22

AaronAaron

1,7411 gold badge10 silver badges22 bronze badges

The variable @sql only lives where it’s defined. Definitions are delimited by a ‘GO’ statement. See if this works for you…

Set @id = '12345'
Set @sql = 'insert into   #temphold(dateadded,dateupdated,id) '
          +'select   getdate(),getdate(),COALESCE(@id,'''') '
GO
PRINT @sql;
execute sp_executesql @statement = @sql, @parameters = N'@id varchar(100)', @id = @id

Drop Table #temphold

answered Jul 31, 2018 at 21:30

Источник

Must declare the scalar variable sql ошибка

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

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”.