Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 207
  • Last Modified:

Stored Procedure return variable

I'm trying to take the ID from one stored procedure INSERT to another stored procedure INSERT.  My first stored procedure is actually inserting the record, but it is inserting NULL into the 2nd parameter in the next stored procedure that tries to use the inserted ID. My 2 execute lines are below, and the first stored procedure is pasted into the code snippet below.

Exec spDCUsers_AddCreditApplication @dcca_firstname,@dcca_middleinitial,@dcca_lastname,@dcca_ssn,@dcca_dob,@dcca_phonehome,@dcca_phonework,@dcca_email,@dcca_address1,@dcca_address2,@dcca_city,@dcca_state, @dcca_zip,@dcca_timeataddressyrs,@dcca_timeataddressmos,@dcca_typeofresidence,@dcca_typeofincome,@dcca_frequency,@dcca_grossamount, @dcca_rentpayment,@dcca_selfemployedorretired,@dcca_employer,@dcca_empaddress1,@dcca_empaddress2,@dcca_empcity,@dcca_empstate, @dcca_empzip, @dcca_empcountry,@dcca_occupation,@dcca_timeonjobyrs, @dcca_timeonjobmos, @CreditAppId OUTPUT

Exec spDCUsers_AddCreditApplicationProductVersion @UserId, @CreditAppId, @dccapv_productid
USE [drivecore]
GO
/****** Object:  StoredProcedure [saturation1].[spDCUsers_AddCreditApplication]    Script Date: 03/14/2011 19:43:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [saturation1].[spDCUsers_AddCreditApplication]
(

@dcca_firstname nvarchar(50),
@dcca_middleinitial nvarchar(1),
@dcca_lastname nvarchar(50),
@dcca_ssn nvarchar(11),
@dcca_dob nvarchar(10),
@dcca_phonehome nvarchar(20),
@dcca_phonework nvarchar(20),
@dcca_email nvarchar(75),
@dcca_address1 nvarchar(100),
@dcca_address2 nvarchar(100),
@dcca_city nvarchar(50),
@dcca_state nvarchar(2),
@dcca_zip nvarchar(10),
@dcca_timeataddressyrs nvarchar(2),
@dcca_timeataddressmos nvarchar(2),
@dcca_typeofresidence nvarchar(20),
@dcca_typeofincome nvarchar(30),
@dcca_frequency nvarchar(20),
@dcca_grossamount nvarchar(8),
@dcca_rentpayment nvarchar(10),
@dcca_selfemployedorretired nvarchar(15),
@dcca_employer nvarchar(100),
@dcca_empaddress1 nvarchar(50),
@dcca_empaddress2 nvarchar(50),
@dcca_empcity nvarchar(50),
@dcca_empstate nvarchar(2),
@dcca_empzip nvarchar(10),
@dcca_empcountry nvarchar(50),
@dcca_occupation nvarchar(75),
@dcca_timeonjobyrs nvarchar(2),
@dcca_timeonjobmos nvarchar(2), 
@CreditAppId INT OUTPUT
)

AS
BEGIN

INSERT INTO [tblDCCreditApplication](
           
[dcca_firstname],
[dcca_middleinitial],
[dcca_lastname],
[dcca_ssn],
[dcca_dob],
[dcca_phonehome],
[dcca_phonework],
[dcca_email],
[dcca_address1], 
[dcca_address2],
[dcca_city],
[dcca_state],
[dcca_zip],
[dcca_timeataddressyrs],
[dcca_timeataddressmos],
[dcca_typeofresidence],
[dcca_typeofincome],
[dcca_frequency],
[dcca_grossamount], 
[dcca_rentpayment],
[dcca_selfemployedorretired],
[dcca_employer],
[dcca_empaddress1], 
[dcca_empaddress2],
[dcca_empcity],
[dcca_empstate],
[dcca_empzip],
[dcca_empcountry],
[dcca_occupation],
[dcca_timeonjobyrs], 
[dcca_timeonjobmos]
)

VALUES (
@dcca_firstname,
@dcca_middleinitial,
@dcca_lastname,
@dcca_ssn,
@dcca_dob,
@dcca_phonehome,
@dcca_phonework,
@dcca_email,
@dcca_address1,
@dcca_address2,
@dcca_city,
@dcca_state, 
@dcca_zip,
@dcca_timeataddressyrs,
@dcca_timeataddressmos,
@dcca_typeofresidence,
@dcca_typeofincome,
@dcca_frequency,
@dcca_grossamount, 
@dcca_rentpayment,
@dcca_selfemployedorretired,
@dcca_employer,
@dcca_empaddress1,
@dcca_empaddress2,
@dcca_empcity,
@dcca_empstate, 
@dcca_empzip, 
@dcca_empcountry,
@dcca_occupation,
@dcca_timeonjobyrs,
@dcca_timeonjobmos
);

DECLARE  @ApplicationId INT          
SET @ApplicationId = SCOPE_IDENTITY();
      

END

Open in new window

0
saturation
Asked:
saturation
1 Solution
 
Paul JacksonCommented:
Change these lines in your first stored procedure :

DECLARE  @ApplicationId INT          
SET @ApplicationId = SCOPE_IDENTITY();

to
SET @CreditAppId = SCOPE_IDENTITY();
0
 
saturationAuthor Commented:
Excellent.   Thanks jacko!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now