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.
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
