CODEWITHSUNDEEP
sql-server
user2320492
user2320492

Reputation: 151

SQL Server: must declare the scalar variable

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

Upvotes: 1

Views: 4205

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

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;

Upvotes: 3

Related Questions