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
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Outputing a field after an INSERT

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)
AS
INSERT 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 parameter
AS

declare @max_no int

select @max_no = Max(ENRL_ID)+1 FROM TESTABLE

and use this for both insert statement & output parameter

Go to Top of Page

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 out
AS
declare @max_no int

select @max_no = Max(ENRL_ID)+1 FROM TESTABLE

INSERT into TESTABLE (ENRL_ID, days_id, XPLN_TXT)
VALUES (@max_n0, @days_id, @XPLN_TXT)

====================================



Go to Top of Page

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.

Go to Top of Page

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.
============

Go to Top of Page

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 this
Select ColumnName From TableName with(locking hint)

But before using this please go thru this topic thoroughly in BOL.

Go to Top of Page

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!

Go to Top of Page

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.

Go to Top of Page
   

- Advertisement -