SCOPE_IDENTITY()

Question

SCOPE_IDENTITY()

thbaig

Hall of Fame

Points: 3006
More actions
May 21, 2012 at 10:41 am

#149947

I am using SCOPE_IDENTITY() to get last generated id in session,scope.
but this is giving me values for all execution at once. I am using it in sp and that is causing some integrity constraint violation . it can be fixed if SCOPE_IDENTITY() give me only one value.
my scenario
.......
WHILE @@FETCH_STATUS = 0
BEGIN
insert into table T1
values ();
SELECT @PageIdIndt = SCOPE_IDENTITY(); --- e.g. it gives 121,123,124
Insert into table T2
values();
SELECT @PageIdIndt_two = SCOPE_IDENTITY();-- its expected values is 321, but it is giving 121,123,321,124
insert into table T3 (col,col2,col3)
values(val1,val2,@PageIdIndt_two);
.......

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Really hard to give advice based on incomplete code snippits. I would look at using the OUTPUT clause and table variable(s) to pass information as well as looking at eliminating the use of a cursor. Of cousre, can't give you a full answer with all the necessary information to do so.

Please read the first article I reference below in my signature block regarding what and how to post the relevant information.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

SCOPE_IDENTITY cannot be returning multiple values. It doesn't work like that. The return type is numeric(38,0).

http://msdn.microsoft.com/en-us/library/ms190315.aspx

As Lynn said, if you can provide some details we can provide some help.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 3

Yeah i thought something looked wrong, when he said it's returning 4 values;

ii had to double check myself and test scope identity to prove it returns one and only one value.

i think that was psuedocode to represent mulitple values inside a *muttering quietly* cursor

CREATE TABLE MYADDRESSES(

EMPNO INT IDENTITY(1,1) PRIMARY KEY,

ENAME VARCHAR(100),

ADDR1 VARCHAR(100),

ADDR2 VARCHAR(100),

CITY VARCHAR(100),

STATECODE VARCHAR(2),

ZIPCODE VARCHAR(100),

PHONE VARCHAR(20),

MOREDATA VARCHAR(100))

--insert some test data

INSERT INTO MYADDRESSES( ENAME, ADDR1, ADDR2, CITY, STATECODE, ZIPCODE, PHONE, MOREDATA)

SELECT 'Kalvin','123 My Imagination St','','Miami','FL','33024','555-1212','likes snowmen'

UNION

SELECT 'Hobbes','123 My Imagination St','','Miami','FL','33024','555-1222','likes to tease calvin'

select SCOPE_IDENTITY()

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

thbaig Hall of Fame Points: 3006 More actions · Answer 4

thbaig

Hall of Fame

Points: 3006

May 21, 2012 at 11:50 am

#1490585

thank you Lowel