June 23, 2017, 08:13:51 AM
News: If your question is answered please put [RESOLVED] in the subject of the initial message.
Pages: [1]   Go Down
Author Topic: MS SQL - SCOPE_IDENTITY(); & multiple inserts **Resolved**  (Read 2420 times)
msimmons
Administrator
*

Stars: +3/-0
Offline Offline

Gender: Male
Posts: 8429



WWW
« on: August 22, 2014, 01:23:44 PM »

ok, I have a sproc that runs a few inserts, assigning the new record identity to a variable, then inserting those into another table. The issue is that some of the inserts don't insert (if no data to insert) but the SCOPE_IDENTITY(); carries from the previous insert...

in the code below, I would expect @NewId2 to be null, but it contains the id from the previous insert.

Code:
CREATE TABLE #tempdemo (
theId int IDENTITY(100,3),
theField varchar(20)
)

DECLARE @NewId1 int
DECLARE @NewId2 int

INSERT INTO
#tempdemo
(theField)
SELECT
'test1'
--this would have a "from table" in a real situation
WHERE
1 = 1

SET @NewId1 = SCOPE_IDENTITY();

INSERT INTO
#tempdemo
(theField)
SELECT
'test2'
--this would have a "from table" in a real situation
WHERE
1 = 2 --obviously fails, in my real situation there are times the insert has nothing to insert

SET @NewId2 = SCOPE_IDENTITY();


select '@NewId1 = ', @NewId1, '@NewId2 = ', @NewId2


drop table #tempdemo

**Edit: the answer lies in:

IF @@ROWCOUNT>0
BEGIN
   SET @NewId2  = SCOPE_IDENTITY()
END
« Last Edit: August 22, 2014, 02:39:59 PM by msimmons » Logged

                 ..... .....

Pages: [1]   Go Up
Print
 
Jump to: