Can a SP obtain the ID of a record created by another SP?

[Luis]

Can one stored procedure obtain the identity value of a record that
was loaded on the database by another stored procedure so that it can
update that record or create a related record on another table?

Example:

A web application called 'Your Lucky Number' has various screens where
users capture information. This info is processed and a result is
produced.

To get this result the user captures data on 'Screen1.asp', clicks a
'Next' button and is redirected to 'Screen2.asp'. Before the
redirection, a stored procedure, 'SProc_Table_1_Upload', uploads the
data that was captured on 'Screen1.asp' to a SQL Server 2000 database
table called 'tblScreen1' and then terminates.

When the user clicks the 'Next' button on 'Screen2.asp' another stored
procedure, 'SProc_Table_2_Upload', uploads the data from 'Screen2.asp'
to the 'tblScreen2' table on the database.

The same happens on 'Screen3.asp' - 'SProc_Table_3_Upload' uploads the
'Screen3.asp' data to the 'tblScreen3' table.

My question is: how do the second and third stored procedures obtain
the identity value of the record that was created by the first stored
procedure so that they can use that ID value to create the related
records on the second and third tables?

Can one stored procedure use SCOPE_IDENTITY() to obtain the identity
value of the record inserted by another stored procedure, ie. can the
second and third stored procedures use SCOPE_IDENTITY() to obtain the
identity value of the record inserted by the first stored procedure?

How? I know it can easily be done from ONE stored procedure, eg:

CREATE PROCEDURE SProc_Upload
@ValueAA NVARCHAR(10),
@ValueBB NVARCHAR(10),
@ValueCC NVARCHAR(10),
@ValueXX NVARCHAR(10),
@ValueYY NVARCHAR(10),
@ValueZZ NVARCHAR(10),
@Value11 NVARCHAR(10),
@Value22 NVARCHAR(10),
@Value33 NVARCHAR(10)
AS
BEGIN
SET NOCOUNT ON
DECLARE @ID INT
INSERT tblScreen1 (AA,BB,CC) VALUES (@ValueAA,@ValueBB,@ValueCC)
SELECT @ID = SCOPE_IDENTITY()
SELECT ID = @ID
INSERT tblScreen2 (ID,XX,YY,ZZ) VALUES
(@ID,@ValueXX,@ValueYY,@ValueZZ)
INSERT tblScreen2 (ID,11,22,33) VALUES
(@ID,@Value11,@Value22,@Value33)
END
GO

But this SProc can only be run after the info on all 3 screens has
been captured. I want to upload the data from each screen immediately
after the user has captured it.

Is this wise? What happens if the user captures the info on Screen 1
and decides that he can't finish now and shuts the browser down half
way through capturing the info on screen 2? Is there a way to remove
the record that was loaded on Table 1 if no record is loaded on Table
2 and Table 3?

Is this the right way to build the application, or should I upload all
the data from the 3 screens to the database only at the end when
Screen 3 has been completed?

[John Beschler]

Since web applications are stateless there would be no way
the subsequent stored procedures could be sure they are
associted with the first stored procedure. The only way to
do what you want is to retrieve the identity value when
the first stored procedure is run and the store that as a
session variable or a cookie and use that in subsequent
pages to correctly associate the data.

HTH,
John

>-----Original Message-----
>Can one stored procedure obtain the identity value of a

record that

>was loaded on the database by another stored procedure so

that it can

>update that record or create a related record on another

table?

>
>Example:
>
>A web application called 'Your Lucky Number' has various

screens where

>users capture information. This info is processed and a

result is

>produced.
>
>To get this result the user captures data

on 'Screen1.asp', clicks a

>'Next' button and is redirected to 'Screen2.asp'. Before

the

>redirection, a stored procedure, 'SProc_Table_1_Upload',

uploads the

>data that was captured on 'Screen1.asp' to a SQL Server

2000 database

>table called 'tblScreen1' and then terminates.
>
>When the user clicks the 'Next' button on 'Screen2.asp'

another stored

>procedure, 'SProc_Table_2_Upload', uploads the data

from 'Screen2.asp'

>to the 'tblScreen2' table on the database.
>
>The same happens on 'Screen3.asp' -

'SProc_Table_3_Upload' uploads the

>'Screen3.asp' data to the 'tblScreen3' table.
>
>My question is: how do the second and third stored

procedures obtain

>the identity value of the record that was created by the

first stored

>procedure so that they can use that ID value to create

the related

>records on the second and third tables?
>
>Can one stored procedure use SCOPE_IDENTITY() to obtain

the identity

>value of the record inserted by another stored procedure,

ie. can the

>second and third stored procedures use SCOPE_IDENTITY()

to obtain the

>identity value of the record inserted by the first stored

procedure?

>
>How? I know it can easily be done from ONE stored

procedure, eg:

>
>CREATE PROCEDURE SProc_Upload
> @ValueAA NVARCHAR(10),
> @ValueBB NVARCHAR(10),
> @ValueCC NVARCHAR(10),
> @ValueXX NVARCHAR(10),
> @ValueYY NVARCHAR(10),
> @ValueZZ NVARCHAR(10),
> @Value11 NVARCHAR(10),
> @Value22 NVARCHAR(10),
> @Value33 NVARCHAR(10)
>AS
>BEGIN
> SET NOCOUNT ON
> DECLARE @ID INT
> INSERT tblScreen1 (AA,BB,CC) VALUES

(@ValueAA,@ValueBB,@ValueCC)

> SELECT @ID = SCOPE_IDENTITY()
> SELECT ID = @ID
> INSERT tblScreen2 (ID,XX,YY,ZZ) VALUES
>(@ID,@ValueXX,@ValueYY,@ValueZZ)
> INSERT tblScreen2 (ID,11,22,33) VALUES
>(@ID,@Value11,@Value22,@Value33)
>END
>GO
>
>But this SProc can only be run after the info on all 3

screens has

>been captured. I want to upload the data from each screen

immediately

>after the user has captured it.
>
>Is this wise? What happens if the user captures the info

on Screen 1

>and decides that he can't finish now and shuts the

browser down half

>way through capturing the info on screen 2? Is there a

way to remove

>the record that was loaded on Table 1 if no record is

loaded on Table

>2 and Table 3?
>
>Is this the right way to build the application, or should

I upload all

>the data from the 3 screens to the database only at the

end when

>Screen 3 has been completed?
>.
>

[Bob Barrows]

Luis wrote:

> Can one stored procedure obtain the identity value of a record that
> was loaded on the database by another stored procedure so that it can
> update that record or create a related record on another table?
>
> Example:
>
> A web application called 'Your Lucky Number' has various screens where
> users capture information. This info is processed and a result is
> produced.

<snip>

> My question is: how do the second and third stored procedures obtain
> the identity value of the record that was created by the first stored
> procedure so that they can use that ID value to create the related
> records on the second and third tables?

You need to return the ID from the first procedure (I suggest an output
parameter) and pass it via parameter to the second and third procedures.
<snip>

> Can one stored procedure use SCOPE_IDENTITY() to obtain the identity
> value of the record inserted by another stored procedure, ie. can the
> second and third stored procedures use SCOPE_IDENTITY() to obtain the
> identity value of the record inserted by the first stored procedure?

No. Each stored procedure has its own scope and SCOPE_IDENTITY will only
return the identity in that scope.
<snip>

> Is this the right way to build the application, or should I upload all
> the data from the 3 screens to the database only at the end when
> Screen 3 has been completed?

That would be my suggestion. One way to do this would be to response.write
the data from each previous screen to hidden fields in the current page.

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.