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 |
|
dean.c4
Starting Member
14 Posts |
 Posted - 2011-06-07 : 14:33:04
|
| I have a stored procedure that I run from a website to insert user data entered into a web form. Now I'm making some changes and I need to get the autoincrement int that I'm using for the primary key of each record when I create them to be used in another table.Can this be done in one stored proc/query or do I have to do it in two steps? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-07 : 14:36:22
|
| You can use either SCOPE_IDENTITY() or the OUTPUT clause in your INSERT statement. Both are documented in Books Online. OUTPUT is preferable as there is a bug with SCOPE_IDENTITY() under parallel execution, so if your server does a lot of inserts it could be incorrect. |
 |
|
|
dean.c4
Starting Member
14 Posts |
Posted - 2011-06-07 : 14:38:39
|
| Great thanks! I'll look up the OUTPUT. |
|
|
|
dean.c4
Starting Member
14 Posts |
Posted - 2011-06-07 : 16:52:40
|
| Now what if I'm wanting to send this value back to my program that is executing the stored proc's? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-07 : 17:00:52
|
| You can capture it in an output parameter, or SELECT it as a result. If you're only ever going to return one row or ID then an output variable will perform better. |
|
|
|
|
|
|
Insert and get autoincrement ID at once?