Solved

Stored Procedure issues with parameters

Posted on 2011-03-14
3
280 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 500 total points
Comment Utility
@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
Comment Utility
The error you get when you remove @CreditAppId suggests you are not removing the OUTPUT keyword.
0
 

Author Closing Comment

by:saturation
Comment Utility
Perfect.   Another similar question to follow.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
truncate SQL 2008 database log file 3 33
Scheduling Jobs for Execution: 4 13
SQL Maintenance Plan 3 13
Sql query 34 16
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now