Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Stored Procedure issues with parameters

Posted on 2011-03-14
3
Medium Priority
?
290 Views
Last Modified: 2012-05-11
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_AddCreditApplication has too many arguments specified."

What am I doing wrong?  



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

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

Open in new window

0
Comment
Question by:saturation
  • 2
3 Comments
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 2000 total points
ID: 35134592
@CreditAppId is not in your list of parameters in the stored procedure :

[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)
)

When you remove @CreditAppId from the exec line are you removing the OUTPUT keyword as well.
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35134600
The error you get when you remove @CreditAppId suggests you are not removing the OUTPUT keyword.
0
 

Author Closing Comment

by:saturation
ID: 35134638
Perfect.   Another similar question to follow.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

916 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