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?thanksFunnyfrog |
|
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] |
|
|
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/ |
|
|
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 identityFunnyfrog |
|
|
shemayb
Posting Yak Master
159 Posts |
Posted - 2007-07-12 : 19:42:19
|
i want the sql server to insert the incremented valueFunnyfrog |
|
|
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] |
|
|
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/ |
|
|
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?thanksFunnyfrog |
|
|
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/ |
|
|
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?thanksFunnyfrog |
|
|
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?thanksFunnyfrog
YEs.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|