Must declare the scalar variable with SELECT statement

Question

I have the following statement:

DECLARE @Nr_Karton int;

SELECT @Nr_Karton = ISNULL(MAX(Nr), 1000)  FROM W_Karton;

SET @Nr_Karton = @Nr_Karton + 1;

INSERT INTO W_Karton (Container_ID, Nr, Beschrieb, CreationDate, Location) 
  VALUES ('1', @Nr_Karton, '', getDate(), 'Bösingen');

But I get the error:

[SQL] SELECT @Nr_Karton = ISNULL(MAX(Nr), 1000) FROM W_Karton

[Err] 42000 - [SQL Server]Must declare the scalar variable "@Nr_Karton".

How to get rid of the error?

Your code looks fine to me. What line does it occur on? As a side note, please don't re-invent identity columns! — Bridge
@Bridge I edited to show full error. What do you mean about reiventing identity columns? — Pandy Legend
The code you posted parses with no error in SSMS 2008, is that really the code that gives the error? How are you executing it? Are you perhaps trying to execute it line by line instead of as a single batch? And as Bridge said, you should use an IDENTITY column instead of generating ID values yourself. — Pondlife
@PandyLegend A column with Identity specification will automatically increment it's value as you insert new rows - that seems to be what you're trying to emulate by getting the highest value currently, adding one, and then adding that back to the table. Too bad it won't work if you've got concurrent sessions/multiple users etc. as you could get conflicts. I believe it's called AUTO_INCREMENT in MySQL. — Bridge
@brian, that is why he has ISNULL(MAX(Nr), 1000) in place. But the error says Variable not defined which is weird. — Kaf

Bridge Bridge · Accepted Answer · 2012-12-14T15:53:01

I did some playing with this. The fictional schema I created was:

CREATE TABLE W_Karton (Container_ID int, Nr int, Beschrieb varchar(1), CreationDate datetime, Location varchar(10))

Whilst it parsed and ran fine on my local 2008R2 box, the same code did not work when pasted into a SQL Fiddle.

However, if you remove all the semi-colons apart from the last one as per this SQL Fiddle you can see it seems to work fine!

I believe it shouldn't make any difference, but if you would rather it worked and don't care about the why, give it a try...

Must declare the scalar variable with SELECT statement

2 Answers