Solved

Stored Procedure return variable

Posted on 2011-03-14
2
198 Views
Last Modified: 2012-05-11
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
Comment
Question by:saturation
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 500 total points
ID: 35134694
Change these lines in your first stored procedure :

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

to
SET @CreditAppId = SCOPE_IDENTITY();
0
 

Author Comment

by:saturation
ID: 35134706
Excellent.   Thanks jacko!
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question