incrementing inserted variables in stored proc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

incrementing inserted variables in stored proc

Author

Topic

shemayb
Posting Yak Master

159 Posts

Posted - 2007-07-12 : 16:09:11

hi,

jz want to ask what am i going to do if i will use the previous inserted id in the last added record and use it again in adding a new record, and i will have a plus 1 to the retrieved inserted id.

Funnyfrog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-12 : 16:11:05

Not necessarily. It depends on how many users you have accesing the same SP at the same time.
Also, it depends on which query hints you are using.

Peter Larsson
Helsingborg, Sweden

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-07-12 : 16:11:24

use identity column which automaticaly creates the increasing values. you don't have to insert into it.
use scope_identity() function to get the last inserted number.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

shemayb
Posting Yak Master

159 Posts

Posted - 2007-07-12 : 16:17:16

if i already retrieved the last inserted id using the @@identity is it automatically incremented the next time i add a new record?

Funnyfrog

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-13 : 00:31:16

quote:
Originally posted by shemayb

if i already retrieved the last inserted id using the @@identity is it automatically incremented the next time i add a new record?

Funnyfrog

Yes

Madhivanan

Failing to plan is Planning to fail

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-13 : 01:02:07

quote:
Originally posted by shemayb

if i already retrieved the last inserted id using the @@identity is it automatically incremented the next time i add a new record?

Funnyfrog

You should be using SCOPE_IDENTITY() instead of @@IDENTITY which is more accurate across scopes. Read up books on line to see the differences in detail.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources