Author |
Topic |
s0110282
Starting Member
4 Posts |
Posted - 2001-12-07 : 14:51:48
|
In my stored procedure, I am inserting fields to a table one of them being 'Max(ENRL_ID)+1'.I would like to pass the 'Max(ItemID)+1' back to the application. How do I go about doing this?Please note: If I do a select statement to get the Max(ENRL_ID) after the insert I can run the chance that another instance created a new Max(ENRL_ID). I want to prevent that from happening.Here is what I have:=================================================== CREATE PROCEDURE [spa_enrl] @days_id int @xpln_txt varchar(2000)ASINSERT into TESTABLE (ENRL_ID, days_id, XPLN_TXT)VALUES ((SELECT Max(ENRL_ID)+1 FROM TESTABLE), @days_id, @XPLN_TXT) =================================================== |
|
shankarc
Starting Member
37 Posts |
Posted - 2001-12-07 : 15:02:40
|
You have to declare a variable as output parameter and the assign the max value to this output parameter.In your case,CREATE PROCEDURE [spa_enrl] @days_id int @xpln_txt varchar(2000),@max_id int out --declare output parameterASdeclare @max_no int select @max_no = Max(ENRL_ID)+1 FROM TESTABLEand use this for both insert statement & output parameter |
|
|
s0110282
Starting Member
4 Posts |
Posted - 2001-12-07 : 15:44:34
|
To clarify, is below what I need to do? If so, doesn't this run the chance that two users try to insert the same ENRL_ID number? Also, isn;t there some type of parameter I need to be able to pass it to the application? Thanks. ==================================CREATE PROCEDURE [spa_enrl] @days_id int,@xpln_txt varchar(2000),@max_id int outASdeclare @max_no intselect @max_no = Max(ENRL_ID)+1 FROM TESTABLEINSERT into TESTABLE (ENRL_ID, days_id, XPLN_TXT)VALUES (@max_n0, @days_id, @XPLN_TXT) ==================================== |
|
|
shankarc
Starting Member
37 Posts |
Posted - 2001-12-07 : 16:12:01
|
You got it. One more thing is you have to assign the value to the output parameter.Select @max_id = @max_no after the insert statement.In order to ensure unique ID to each user you need to specify explicit locking in the SELECT Max(ENRL_ID) statement. |
|
|
s0110282
Starting Member
4 Posts |
Posted - 2001-12-07 : 16:20:05
|
This will probably be my last question:How do you do explicit locking?A big thanks!===============In order to ensure unique ID to each user you need to specify explicit locking in the SELECT Max(ENRL_ID) statement.============ |
|
|
shankarc
Starting Member
37 Posts |
Posted - 2001-12-07 : 16:28:12
|
I'm not an expert in this. However i can suggest you to look for Locking Hints in SQL server Books Online.You have to specify something like thisSelect ColumnName From TableName with(locking hint)But before using this please go thru this topic thoroughly in BOL. |
|
|
s0110282
Starting Member
4 Posts |
Posted - 2001-12-07 : 20:54:38
|
I'm not sure if I would like the locking since it can get pretty slow and messy with many users. I'm considering maybe something with 'trans' and 'commit' (I think those or something of the sort is an SQL function). This way if there is an error with duplication of the primary key (ENRL_ID) then it won't go through. At least this is what I'm hoping!If anybody else has idea's please share! |
|
|
barmalej
Starting Member
40 Posts |
Posted - 2001-12-08 : 10:43:11
|
Two suggestions:1. If possible (if you know user name who calls your procedure) add a field 'userID' to your table and fill it while INSERTing with function USER_ID(). Then you simply return max ENRL_ID belonging to the current user.2. Another way. Define a variable @MyId and before inserting update it using the function NEWID(). Then use it instead of user name in 1.Pehaps there are similar ways if you do not like locking tables for a while.Hope this helps. |
|
|
|