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
 increment the retrieved value using @@identity

Author  Topic 

shemayb
Posting Yak Master

159 Posts

Posted - 2007-07-12 : 19:34:53
hi,

i really need ur help..

I am creating an a stored procedure which could insert new records, the identity of the primary key in my table is not incremented. i want to increment the id when adding a new record in the stored procedure. what would be the system for that?

thanks

Funnyfrog

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-12 : 19:38:01
is the primary key an identity column ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-12 : 19:39:55
So do you want to manually insert the Id or yould you rather have SQL Server insert the next available incremented value for you?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

shemayb
Posting Yak Master

159 Posts

Posted - 2007-07-12 : 19:40:33
the primary key is not an identity column..it is not set as an identity

Funnyfrog
Go to Top of Page

shemayb
Posting Yak Master

159 Posts

Posted - 2007-07-12 : 19:42:19
i want the sql server to insert the incremented value

Funnyfrog
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-12 : 19:45:06
then set the column as an identity column


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-12 : 19:45:20
Go to the table design view and change the IDENTITY property for the column to Yes. Choose the seed value (increment by 1 or X).

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

shemayb
Posting Yak Master

159 Posts

Posted - 2007-07-12 : 19:47:41
you mean I can't use the @@identity function if the identity is not set to yes?


thanks

Funnyfrog
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-12 : 19:49:35
(1) @@Identity will retrieve the ID of the last inserted row IF the column is set as an Identity column.
(2) Use SCOPE_IDENTITY() instead of @@IDENTITY as it is more accurate. Read up books on line for identity columns to get more info and the differences between SCOPE_IDENTITY() and @@IDENTITY.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

shemayb
Posting Yak Master

159 Posts

Posted - 2007-07-12 : 19:53:30
i can't also use the scope_identity if the identity is not set to yes? am i right?
thanks

Funnyfrog
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-12 : 20:09:32
quote:
Originally posted by shemayb

i can't also use the scope_identity if the identity is not set to yes? am i right?
thanks

Funnyfrog



YEs.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page
   

- Advertisement -