SQL Server: must declare the scalar variable

Question

I'm working on SQL Server 2012. I try to declare two variables @max and @rc and use them in the insert statement:

DECLARE @max INT 
SET @max = 100000

DECLARE @rc INT
SET @rc = 1

INSERT INTO dbo.Nums VALUES(1);

WHILE @rc * 2 <= @max
BEGIN
    INSERT INTO dbo.Nums 
       SELECT n + @rc 
       FROM dbo.Nums

    SET @rc = @rc * 2
END

INSERT INTO dbo.Nums 
   SELECT n + @rc 
   FROM dbo.Nums 
   WHERE n + @rc <= @max
GO

I get this error (four times):

[Error Code: 137, SQL State: 37000]
[Microsoft][ODBC SQL Server Driver][SQL Server]
Must declare the scalar variable "@rc".

When I try the following:

DECLARE @max INT
SET @max = 100000

DECLARE @rc INT
SET @rc = 1

INSERT INTO dbo.Nums VALUES(1);

WHILE @rc * 2 <= @max
BEGIN
    DECLARE @rc     INT
    DECLARE @max    INT

    SET @max = 100000

    INSERT INTO dbo.Nums 
       SELECT n + @rc 
       FROM dbo.Nums

    SET @rc = @rc * 2
END

INSERT INTO dbo.Nums 
   SELECT n + @rc 
   FROM dbo.Nums 
   WHERE n + @rc <= @max
GO

I only get it once:

[Error Code: 137, SQL State: 37000]
[Microsoft][ODBC SQL Server Driver][SQL Server]
Must declare the scalar variable "@rc".

Can you help me figure out the problem?

Thanks

Are you selecting all the lines of SQL to run? It runs fine for me and I think you're not selecting the lines that declare the variables when you're executing. — Ola Ekdahl
Thanks for reply. I use DbVisualizer to run the command. it does not allow selecting lines I put the whole code and simple execute it. — user2320492
Must be a DbVisualizer quirk/problem. Works fine in SSMS; there is nothing wrong with the SQL. — Tab Alleman
What version @TabAlleman. In 2008r2 it complains because the variables are declared inside the loop. — Sean Lange
Removing the semi-colon fixed the "problem"? Your client tool is severely broken. See if there's a setting somewhere that allows you to specify a batch separator - seems the default is semi-colon, but IMHO you should make it GO to be consistent with native tools. The last thing you should be doing is removing semi-colons. — Aaron Bertrand

Sean Lange Sean Lange · Accepted Answer · 2015-02-25T19:35:32

Since this looks like you are trying to populate a numbers table here is another way to do that. It doesn't use a loop and is quite a bit simpler.

DECLARE @max INT;
SET @max = 100000;

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    E5(N) as (select 1 from E4, E1),
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E5
    )

INSERT INTO dbo.Nums 
select N from cteTally
where N <= @max;

SQL Server: must declare the scalar variable

1 Answers