Solved

Stored Procedure issues with parameters

Posted on 2011-03-14
3
285 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
[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
3 Comments
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 500 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

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parse this column 6 38
SQL Server maintenance plan 8 61
How can I find this data? 3 40
Trying to understand why my Index is so large 12 47
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…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

740 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