Returning/storing a value for use in a stored proc.

Hi:
I have a stored procedure which performs an insert statement. At the
conclusion of the insert, I have SELECT SCOPE_IDENTITY(). Now, I want to be
able to use this value for the next stored procedure (which will then perfor
m
an update on the newly inserted record). Here's an example:
--Step #1:
Execute spCCD @.COMPANY_ID
--which looks like this:
CREATE PROCEDURE [dbo].[spSTMNT_CCDPPD]
@.Company_ID
Declare @.STMNT_ID int
AS
Insert Into STMNT (
COMPANY_ID,
Tran_Fee1,
Tran_Fee2
)
Values (
@.COMPANY_ID,
0.15,
0.25
)
Set @.STMNT_ID = (SELECT SCOPE_IDENTITY())
--Step #2
Execute spVoid @.STMNT_ID
---
So calling the stored procedures will look like this:
Execute spSTMNT_CCDPPD @.COMPANY_ID --this will return @.STMNT_ID
Execute spVoid @.STMNT_IDEric,
Use an output parameter.
CREATE PROCEDURE [dbo].[spSTMNT_CCDPPD]
@.Company_ID,
@.STMNT_ID int output
AS
set nocount on
Insert Into STMNT (
COMPANY_ID,
Tran_Fee1,
Tran_Fee2
)
Values (
@.COMPANY_ID,
0.15,
0.25
)
Set @.STMNT_ID = SCOPE_IDENTITY()
go
declare @.STMNT_ID int
Execute spSTMNT_CCDPPD @.COMPANY_ID, @.STMNT_ID output
Execute spVoid @.STMNT_ID
go
Remember to handle errors.
AMB
"Eric" wrote:

> Hi:
> I have a stored procedure which performs an insert statement. At the
> conclusion of the insert, I have SELECT SCOPE_IDENTITY(). Now, I want to
be
> able to use this value for the next stored procedure (which will then perf
orm
> an update on the newly inserted record). Here's an example:
> --Step #1:
> Execute spCCD @.COMPANY_ID
> --which looks like this:
> CREATE PROCEDURE [dbo].[spSTMNT_CCDPPD]
> @.Company_ID
> Declare @.STMNT_ID int
> AS
> Insert Into STMNT (
> COMPANY_ID,
> Tran_Fee1,
> Tran_Fee2
> )
> Values (
> @.COMPANY_ID,
> 0.15,
> 0.25
> )
> Set @.STMNT_ID = (SELECT SCOPE_IDENTITY())
> --Step #2
> Execute spVoid @.STMNT_ID
> ---
> So calling the stored procedures will look like this:
> Execute spSTMNT_CCDPPD @.COMPANY_ID --this will return @.STMNT_ID
> Execute spVoid @.STMNT_ID
>|||Correction,

> @.Company_ID,
CREATE PROCEDURE [dbo].[spSTMNT_CCDPPD]
@.Company_ID int,
@.STMNT_ID int output
AS
set nocount on
Insert Into STMNT (
COMPANY_ID,
Tran_Fee1,
Tran_Fee2
)
Values (
@.COMPANY_ID,
0.15,
0.25
)
Set @.STMNT_ID = SCOPE_IDENTITY()
go
declare @.COMPANY_ID int
declare @.STMNT_ID int
set @.COMPANY_ID = 123
Execute spSTMNT_CCDPPD @.COMPANY_ID, @.STMNT_ID output
Execute spVoid @.STMNT_ID
go
AMB
"Alejandro Mesa" wrote:
> Eric,
> Use an output parameter.
> CREATE PROCEDURE [dbo].[spSTMNT_CCDPPD]
> @.Company_ID,
> @.STMNT_ID int output
> AS
> set nocount on
> Insert Into STMNT (
> COMPANY_ID,
> Tran_Fee1,
> Tran_Fee2
> )
> Values (
> @.COMPANY_ID,
> 0.15,
> 0.25
> )
> Set @.STMNT_ID = SCOPE_IDENTITY()
> go
> declare @.STMNT_ID int
> Execute spSTMNT_CCDPPD @.COMPANY_ID, @.STMNT_ID output
> Execute spVoid @.STMNT_ID
> go
> Remember to handle errors.
>
> AMB
> "Eric" wrote:
>