Problem Assigning the newly created EmployeeId in to local variable??

[mrpro]

Hello all,

i am Creating New EmployeeId in the Employees table using this statement.

BEGIN TRAN

INSERT INTO Employees(EmployeeId,) 
SELECT 1 + COALESCE(MAX(EmployeeId), 0)
FROM EMPLOYEES

COMMIT TRAN

Is there any way i should store this newly created EmployeeId in a local variable so that i can use it for other purposes

Thanks

 

[vongrunt]

Something like:

DECLARE @BLAH INT

BEGIN TRAN

SELECT @BLAH = 1 + COALESCE(MAX(EmployeeId), 0)
FROM EMPLOYEES

INSERT INTO Employees(EmployeeId) 
VALUES ( @BLAH )

COMMIT TRAN

Btw. this looks like job for vanilla identity column.

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[mrpro]

Thanks for the reply vongrunt but i think to my knowledge though a beginner

by the time you use

SELECT @BLAH = 1 + COALESCE(MAX(EmployeeId), 0)

statement some one might have already insert a record in to Employee so that's the reason i wanted to use

INSERT INTO Employees(EmployeeId,) 
SELECT 1 + COALESCE(MAX(EmployeeId), 0)
FROM EMPLOYEES

but still wanted to hold this new value some how..

any help will be appreciated..

Thanks

 

[vongrunt]

Variable assignment and INSERT are not possible within single statement.

To solve this possible problem, you'll have to set higher transaction isolation level or play with lock hints (see TABLOCK*/HOLDLOCK in BOL).

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.