saturation
asked on
Stored Procedure issues with parameters
I'm trying to execute the following stored procedure line (the stored procedure is below, too). If I take out the last parameter (@CreditAppId) in my execute line, I get
"The formal parameter "@dcca_timeonjobmos" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output."
If I leave it in (@CreditAppId) as it is below, I get
"Procedure or function spDCUsers_AddCreditApplica tion has too many arguments specified."
What am I doing wrong?
Exec spDCUsers_AddCreditApplica tion @dcca_firstname,@dcca_midd leinitial, @dcca_last name,@dcca _ssn,@dcca _dob,@dcca _phonehome ,@dcca_pho nework,@dc ca_email,@ dcca_addre ss1,@dcca_ address2,@ dcca_city, @dcca_stat e, @dcca_zip,@dcca_timeataddr essyrs,@dc ca_timeata ddressmos, @dcca_type ofresidenc e,@dcca_ty peofincome ,@dcca_fre quency,@dc ca_grossam ount, @dcca_rentpayment,@dcca_se lfemployed orretired, @dcca_empl oyer,@dcca _empaddres s1,@dcca_e mpaddress2 ,@dcca_emp city,@dcca _empstate, @dcca_empzip, @dcca_empcountry,@dcca_occ upation,@d cca_timeon jobyrs, @dcca_timeonjobmos, @CreditAppId OUTPUT
"The formal parameter "@dcca_timeonjobmos" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output."
If I leave it in (@CreditAppId) as it is below, I get
"Procedure or function spDCUsers_AddCreditApplica
What am I doing wrong?
Exec spDCUsers_AddCreditApplica
USE [myDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [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)
)
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The error you get when you remove @CreditAppId suggests you are not removing the OUTPUT keyword.
ASKER
Perfect. Another similar question to follow.