Must declare the scalar variable

Question

I wrote this SQL in a stored procedure but not working   declare  tableName varchar max     TblTest  declare  col1Name varchar max     VALUE1  declare  col2Name varchar max     VALUE2  declare  value1 varchar max  declare  value2 varchar 200   execute  Select TOP 1  value1    col1Name     value2    col2Name   From     tableName    Where ID   61    select  value1  execute  Select TOP 1  value1 VALUE1   value2 VALUE2 From TblTest Where ID   61     This SQL throws this error       Must declare the scalar variable   value1     I am generating the SQL dynamically and I want to get value in a variable  What should I do

User · Answer

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

User · Answer

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 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 an 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   lt -- remove this character to avoid the error message SELECT   FROM AnotherTable WHERE MyTableId    var

User · Answer

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  quot GO quot  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  quot Must declare the scalar variable xy quot  was gone

User · Answer

-- CREATE OR ALTER PROCEDURE   ALTER PROCEDURE out     age INT   salary INT OUTPUT    AS BEGIN  SELECT  salary    SELECT SALARY FROM new testing where AGE    age  ORDER BY AGE OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY     END  -----------------DECLARE THE OUTPUT VARIABLE---------------------------------  DECLARE  test INT  ---------------------THEN EXECUTE THE QUERY---------------------------------  EXECUTE out 25    salary    test OUTPUT  print  test  -------------------same output obtain without procedure------------------------------------------- SELECT   FROM new testing where AGE   25 ORDER BY AGE OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY

User · Answer

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  gt    from          AND Timestamp  lt    till      USE COMMA NOT CONCATENATE       new SqlParameter  from   from       new SqlParameter  till   till         ToListAsync        Result

User · Answer

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

User · Answer

You can also get this error message if a variable is declared before a GOand referenced after it   See this question and this workaround

User · Answer

Declare  v1 varchar max    v2 varchar 200    Declare  sql nvarchar max    Set  sql   N SELECT  v1   value1   v2   value2 FROM dbo TblTest -- always use schema WHERE ID   61     EXEC sp executesql  sql    N  v1 varchar max  output   v2 varchar 200  output      v1 output   v2 output    You should also pass your input  like wherever 61 comes from  as proper parameters  but you won t be able to pass table and column names that way

User · Answer

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

User · Answer

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 don t really need to re-declare  Rt etc  inside the  sql string  you just need to say   SET  sql    sql     WHERE RowNum BETWEEN          CONVERT varchar 12    RowFrom      AND          CONVERT varchar 12    RowTo     Though it would be better to have proper parameterization  e g   SET  sql    sql     WHERE RowNum BETWEEN  RowFrom AND  RowTo     EXEC sys sp executesql  sql    N  RowFrom int   RowTo int      RowFrom   RowTo

User · Answer

Here is a simple example     Create or alter PROCEDURE getPersonCountByLastName    lastName varchar 20    count int OUTPUT   As Begin select  count   count personSid  from Person where lastName like  lastName End    Execute below statements in one batch  by selecting all   1  Declare  count int 2  Exec getPersonCountByLastName kumar   count output 3  Select  count   When i tried to execute statements 1 2 3 individually  I had the same error  But when executed them all at one time  it worked fine   The reason is that SQL executes declare  exec statements in different sessions    Open to further corrections

User · Answer

The reason you are getting the DECLARE error from your dynamic statement is because dynamic statements are handled in separate batches  which boils down to a matter of scope   While there may be a more formal definition of the scopes available in SQL Server  I ve found it sufficient to generally keep the following three in mind  ordered from highest availability to lowest availability   Global    Objects that are available server-wide  such as temporary tables created with a double hash pound sign     GLOBALTABLE  however you like to call       Be very wary of global objects  just as you would with any application  SQL Server or otherwise  these types of things are generally best avoided altogether   What I m essentially saying is to keep this scope in mind specifically as a reminder to stay out of it   IF   OBJECT ID   tempdb dbo   GlobalTable    IS NULL   BEGIN     CREATE TABLE   GlobalTable               Val             BIT             INSERT INTO   GlobalTable   Val       VALUES   1    END  GO  -- This table may now be accessed by any connection in any database  -- assuming the caller has sufficient privileges to do so  of course    Session    Objects which are reference locked to a specific spid   Off the top of my head  the only type of session object I can think of is a normal temporary table  defined like  Table   Being in session scope essentially means that after the batch   terminated by GO   completes  references to this object will continue to resolve successfully   These are technically accessible by other sessions  but it would be somewhat of a feat do to so programmatically as they get sort of randomized names in tempdb and accessing them is a bit of a pain in the ass anyway   -- Start of session  -- Start of batch  IF   OBJECT ID   tempdb dbo  t Test    IS NULL   BEGIN     CREATE TABLE  t Test               Val     BIT             INSERT INTO  t Test   Val       VALUES   1    END  GO  -- End of batch   -- Start of batch  SELECT    FROM     t Test  GO -- End of batch    Opening a new session   a connection with a separate spid    the second batch above would fail  as that session would be unable to resolve the  t Test object name   Batch    Normal variables  such as your  value1 and  value2  are scoped only for the batch in which they are declared   Unlike  Temp tables  as soon as your query block hits a GO  those variables stop being available to the session   This is the scope level which is generating your error   -- Start of session  -- Start of batch  DECLARE  test   BIT   1   PRINT  test  GO -- End of batch   -- Start of batch  PRINT  Test   -- Msg 137  Level 15  State 2  Line 2               -- Must declare the scalar variable   Test   GO -- End of batch    Okay  so what   What is happening here with your dynamic statement is that the EXECUTE   command effectively evaluates as a separate batch  without breaking the batch you executed it from   EXECUTE   is good and all  but since the introduction of sp executesql    I use the former only in the most simple of instances   explicitly  when there is very little  dynamic  element of my statements at all  primarily to  trick  otherwise unaccommodating DDL CREATE statements to run in the middle of other batches      AaronBertrand s answer above is similar and will be similar in performance to the following  leveraging the function of the optimizer when evaluating dynamic statements  but I thought it might be worthwhile to expand on the  param  well  parameter   IF NOT EXISTS   SELECT  1                 FROM    sys objects                 WHERE   name    TblTest                      AND type    U    BEGIN     --DROP TABLE dbo TblTest      CREATE TABLE dbo TblTest               ID      INTEGER          VALUE1  VARCHAR  1            VALUE2  VARCHAR  1               INSERT INTO dbo TblTest   ID  VALUE1  VALUE2       VALUES   61   A    B     END   SET NOCOUNT ON   DECLARE  SQL    NVARCHAR  MAX             PRM    NVARCHAR  MAX             value1 VARCHAR  MAX             value2 VARCHAR  200             Table  VARCHAR  32             ID     INTEGER       SET  Table    TblTest       SET  ID   61       SET  PRM               ID        INTEGER            value1    VARCHAR  MAX   OUT            value2    VARCHAR  200   OUT       SET  SQL             SELECT    value1   VALUE1                    value2   VALUE2         FROM    dbo      REPLACE   Table                          WHERE   ID     ID     EXECUTE dbo sp executesql  statement    SQL   param    PRM                ID    ID    value1    value1 OUT    value2    value2 OUT   PRINT  value1          value2   SET NOCOUNT OFF

User · Answer

This will occur in SQL Server as well if you don t run all of the statements at once   If you are highlighting a set of statements and executing the following    DECLARE  LoopVar INT SET  LoopVar    SELECT COUNT    FROM SomeTable    And then try to highlight another set of statements such as    PRINT  LoopVar is      CONVERT NVARCHAR 255    LoopVar    You will receive this error

[sql-server] Must declare the scalar variable

Examples related to sql-server

Examples related to tsql

Examples related to stored-procedures

Examples related to executequery