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.
| Author |
Topic |
|
chinlax
Starting Member
30 Posts |
 Posted - 2011-10-12 : 03:29:41
|
| Hi All,I have an table employ, i have written stored procedure for inserting data.the employ table has columns eid, ename, sal, didcondition :i should pass the values through the stored procedure --exec spinsert @ename='kiran',@sal=2000,@did=1but i should not pass eid, it should be automatically inserted and i should not use identity column as well.i tried several ways but the same eid is repeating for all the recordsi want it to increment and store it in eid....how to proceed .......Thanks in advance |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2011-10-12 : 03:35:38
|
inside your stored procedure you can select max(eid) and add 1...
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-12 : 05:05:44
|
| but i should not pass eid, it should be automatically inserted and i should not use identity column as well.why you dont want to use identity column if it has to autoincrement?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-10-12 : 05:32:50
|
| In the spinsert tbl (..)select id = coalesce((select max(id) from tbl)+1,1), ...If you want to cater for multi-row inserts then you can use row_number()If this is run concurrently from different connections you might also need to hold a lock on the table to make the selects sequential.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
stored procedure for autoincrement