|
Home > Archive > microsoft.public.sqlserver.server > October 2002 > Too Many Arguments Specified
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Too Many Arguments Specified
|
|
| Jim Lewis 2002-10-05, 9:12 pm |
| I am having trouble with a Stored Procedure I have 26 parameters specified but, I am getting an error that too many arguments are specified. I was executing this Stored Procedure through an ADO command object but because I could not get it to execute I trapped the statement in SQL Profiler and tried to execute the statement in the Query Analyzer. This is the error I receive "Server: Msg 8144, Level 16, State 2, Procedure spCustomerInsert, Line 0 Procedure or function spCustomerInsert has too many arguments specified." I have counted out the parameters and I have 26, I even did the @PramaterName = "value" for each parameter and it all worked out perfectly, except it will not execute. If anyone knows what the problem is I would appreciate your help.
Thanks
-Jim
The execution statement and code are below:
declare @P1 bigint
set @P1=NULL
declare @P2 bigint
set @P2=NULL
declare @P3 bigint
set @P3=NULL
declare @P4 bigint
set @P4=NULL
declare @P5 bigint
set @P5=NULL
declare @P6 int
set @P6=NULL
declare @P7 varchar(2000)
set @P7=NULL
exec spCustomerInsert 'First Last Name', 0, NULL, 7145551212, NULL, NULL, 'jlewis', '555 Mocking Bird Lane', '556 Mocking Bird Lane', NULL, NULL, NULL, NULL, 5, 5, 55555, 55555, 'Long Beach', 'Seal Beach', @P1 output, @P2 output, @P3 output, @P4 output, @P5 output, @P6 output, @P7 output
select @P1, @P2, @P3, @P4, @P5, @P6, @P7
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spZipCodeSelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spZipCodeSelect]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE spCustomerInsert(
@cCustName varchar(200) = NULL,
@bReseller bit = NULL,
@iReslrNum int = NULL,
@iDayPhone bigint = NULL,
@iNightPhone bigint = NULL,
@cEmail Varchar(100) = NULL,
@cOrderTaker Varchar(100) = NULL,
@cAddress1BT varchar(200)= NULL,
@cAddress1ST varchar(200)= NULL,
@cAddress2BT Varchar(200) = NULL,
@cAddress2ST Varchar(200) = NULL,
@iZipIDBT int = NULL,
@iZipIDST int = NULL,
@iStateIDBT int = NULL,
@iStateIDST int = NULL,
@iZipCodeBT int = NULL,
@iZipCodeST int = NULL,
@cCityNameBT Varchar(200),
@cCityNameST Varchar(200),
@iCustomerID int = NULL OUTPUT,
@iCustAddressIDBT int = NULL OUTPUT,
@iCustAddressIDST int = NULL OUTPUT,
@iZipIDBTOut int = NULL OUTPUT,
@iZipIDSTOut int = NULL OUTPUT,
@iRetCode int = NULL OUTPUT,
@cRetMsg varchar(2000) = NULL OUTPUT)
AS
SET NOCOUNT ON
DECLARE @iCheckCust int
--Validate incoming parameters
SELECT @iRetCode = 1, @cRetMsg = ''
IF @cCustName IS NULL
SELECT @iRetCode = 0, @cRetMsg = @cRetMsg + 'Customer Name is required.' + CHAR(13) + CHAR(10)
IF @iDayPhone IS NULL
SELECT @iRetCode = 0, @cRetMsg = @cRetMsg + 'Day Phone Number is required.' + CHAR(13) + CHAR(10)
IF @cAddress1BT IS NULL or @cAddress1ST IS NULL
SELECT @iRetCode = 0, @cRetMsg = @cRetMsg + 'Address 1 is required.' + CHAR(13) + CHAR(10)
IF @cCityNameBT IS NULL or @cCityNameST IS NULL
SELECT @iRetCode = 0, @cRetMsg = @cRetMsg + 'City Name is required.' + CHAR(13) + CHAR(10)
IF @iZipCodeBT IS NULL or @iZipCodeST IS NULL
SELECT @iRetCode = 0, @cRetMsg = @cRetMsg + 'Zip Code is required.' + CHAR(13) + CHAR(10)
--If required info does not exist, exit proc
IF @iRetCode = 0
RETURN
--Check for existence of Customer
SELECT @iCheckCust = CustomerID FROM Customer WHERE CustName = @cCustName and DayPhone = @iDayPhone
IF @iCheckCust IS NOT NULL
SELECT @iRetCode = 0, @cRetMsg = @cRetMsg + 'Customer already exist.' + CHAR(13) + CHAR(10)
--If Customer found, exit proc
IF @iRetCode = 0
RETURN
--Insert Customer Information
BEGIN TRAN
INSERT INTO Customer (CustName, Reseller, ReslrNum, DayPhone, NightPhone, Email, CustEnterDate, OrderTaker)
VALUES(@cCustName, @bReseller, @iReslrNum, @iDayPhone, @iNightPhone, @cEmail, getdate(), @cOrderTaker)
-- Check if insert succeeded. If so, get CustomerID.
IF @@ROWCOUNT = 1
SELECT @iCustomerID = @@IDENTITY, @iRetCode = 1
ELSE
SELECT @iCustomerID = 0, @iRetCode = 0, @cRetMsg = 'Insertion of new Customer failed.'
IF @iRetCode = 0
BEGIN
ROLLBACK TRAN
RETURN
END
--Determine if ZipCode Exist
IF @iZipIDBT IS NULL
SELECT @iZipIDBT = ZipID from Zip where ZipCode = @iZipCodeBT and CityName = @cCityNameBT
IF @iZipIDST IS NULL
SELECT @iZipIDST = ZipID from Zip where ZipCode = @iZipCodeST and CityName = @cCityNameST
--Insert New Bill To Zip Code
IF @iZipIDBT IS NULL
BEGIN
INSERT INTO ZIP (StateID, ZipCode, CityName) VALUES (@iStateIDBT, @iZipCodeBT, @cCityNameBT)
-- Check if insert succeeded. If so, get ZipID.
IF @@ROWCOUNT = 1
BEGIN
SELECT @iZIPIDBTOut = @@IDENTITY, @iRetCode = 1
SELECT @iZIPIDBT = @iZIPIDBTOut
END
ELSE
SELECT @iZIPIDBTOut = 0, @iRetCode = 0, @cRetMsg = 'Insertion of new Bill To Zip Code failed.'
IF @iRetCode = 0
BEGIN
ROLLBACK TRAN
RETURN
END
End
--Insert New Ship To Zip Code
IF @iZipIDST IS NULL
BEGIN
INSERT INTO ZIP (StateID, ZipCode, CityName) VALUES (@iStateIDST, @iZipCodeST, @cCityNameST)
-- Check if insert succeeded. If so, get ZipID.
IF @@ROWCOUNT = 1
Begin
SELECT @iZIPIDSTOut = @@IDENTITY, @iRetCode = 1
SELECT @iZIPIDST = @iZIPIDSTOut
END
ELSE
SELECT @iZIPIDSTOut = 0, @iRetCode = 0, @cRetMsg = 'Insertion of new Ship To Zip Code failed.'
IF @iRetCode = 0
BEGIN
ROLLBACK TRAN
RETURN
END
End
--Insert Customer Bill To Address
INSERT INTO CustAddress (CustomerID, Address1, Address2,ZipID) VALUES (@iCustomerID, @cAddress1BT,
@cAddress2BT, @iZipIDBT)
IF @@ROWCOUNT = 1
SELECT @iCustAddressIDBT = @@IDENTITY, @iRetCode = 1
ELSE
SELECT @iCustAddressIDBT = 0, @iRetCode = 0, @cRetMsg = 'Insertion of Bill To Customer Address failed.'
IF @iRetCode = 0
BEGIN
ROLLBACK TRAN
RETURN
END
--Insert Customer Ship To Address
INSERT INTO CustAddress (CustomerID, Address1, Address2,ZipID, ShipTo) VALUES (@iCustomerID, @cAddress1ST,
@cAddress2ST, @iZipIDST,1)
IF @@ROWCOUNT = 1
SELECT @iCustAddressIDST = @@IDENTITY, @iRetCode = 1
ELSE
SELECT @iCustAddressIDST = 0, @iRetCode = 0, @cRetMsg = 'Insertion of Ship To Customer Address failed.'
IF @iRetCode = 0
BEGIN
ROLLBACK TRAN
RETURN
END
-- Test to see if all details inserted
IF @@ERROR = 0
BEGIN
COMMIT TRAN
SELECT @iRetCode = 1, @cRetMsg = 'Customer' + @cCustName + ' added successfully.'
END
ELSE
BEGIN
ROLLBACK TRAN
SELECT @iCustomerID = 0, @iRetCode = 0, @cRetMsg = 'Insertion of new Customer failed.'
END
RETURN
GO
| |
| Gert-Jan Strik 2002-10-05, 9:12 pm |
| Just a tip: make sure that the literal string parameters do not have any
single quotes in them, or make sure they are handled properly (by
replacing one single quote with two). In you example below, they are
just fine though.
Hope this helps,
Gert-Jan
> Jim Lewis wrote:
>
> I am having trouble with a Stored Procedure I have 26 parameters
> specified but, I am getting an error that too many arguments are
> specified. I was executing this Stored Procedure through an ADO
> command object but because I could not get it to execute I trapped the
> statement in SQL Profiler and tried to execute the statement in the
> Query Analyzer. This is the error I receive "Server: Msg 8144, Level
> 16, State 2, Procedure spCustomerInsert, Line 0 Procedure or function
> spCustomerInsert has too many arguments specified." I have counted
> out the parameters and I have 26, I even did the @PramaterName =
> "value" for each parameter and it all worked out perfectly, except it
> will not execute. If anyone knows what the problem is I would
> appreciate your help.
>
> Thanks
> -Jim
>
> The execution statement and code are below:
>
> declare @P1 bigint
> set @P1=NULL
> declare @P2 bigint
> set @P2=NULL
> declare @P3 bigint
> set @P3=NULL
> declare @P4 bigint
> set @P4=NULL
> declare @P5 bigint
> set @P5=NULL
> declare @P6 int
> set @P6=NULL
> declare @P7 varchar(2000)
> set @P7=NULL
> exec spCustomerInsert 'First Last Name', 0, NULL, 7145551212, NULL,
> NULL, 'jlewis', '555 Mocking Bird Lane', '556 Mocking Bird Lane',
> NULL, NULL, NULL, NULL, 5, 5, 55555, 55555, 'Long Beach', 'Seal
> Beach', @P1 output, @P2 output, @P3 output, @P4 output, @P5 output,
> @P6 output, @P7 output
> select @P1, @P2, @P3, @P4, @P5, @P6, @P7
>
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[spZipCodeSelect]') and OBJECTPROPERTY(id,
> N'IsProcedure') = 1)
> drop procedure [dbo].[spZipCodeSelect]
> GO
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE PROCEDURE spCustomerInsert(
>
> @cCustName varchar(200) = NULL,
> @bReseller bit = NULL,
> @iReslrNum int = NULL,
> @iDayPhone bigint = NULL,
> @iNightPhone bigint = NULL,
> @cEmail Varchar(100) = NULL,
> @cOrderTaker Varchar(100) = NULL,
> @cAddress1BT varchar(200)= NULL,
> @cAddress1ST varchar(200)= NULL,
> @cAddress2BT Varchar(200) = NULL,
> @cAddress2ST Varchar(200) = NULL,
> @iZipIDBT int = NULL,
> @iZipIDST int = NULL,
> @iStateIDBT int = NULL,
> @iStateIDST int = NULL,
> @iZipCodeBT int = NULL,
> @iZipCodeST int = NULL,
> @cCityNameBT Varchar(200),
> @cCityNameST Varchar(200),
> @iCustomerID int = NULL OUTPUT,
> @iCustAddressIDBT int = NULL OUTPUT,
> @iCustAddressIDST int = NULL OUTPUT,
> @iZipIDBTOut int = NULL OUTPUT,
> @iZipIDSTOut int = NULL OUTPUT,
> @iRetCode int = NULL OUTPUT,
> @cRetMsg varchar(2000) = NULL OUTPUT)
>
> AS
> SET NOCOUNT ON
>
> DECLARE @iCheckCust int
>
> --Validate incoming parameters
> SELECT @iRetCode = 1, @cRetMsg = ''
>
> IF @cCustName IS NULL
> SELECT @iRetCode = 0, @cRetMsg = @cRetMsg + 'Customer Name is
> required.' + CHAR(13) + CHAR(10)
>
> IF @iDayPhone IS NULL
> SELECT @iRetCode = 0, @cRetMsg = @cRetMsg + 'Day Phone Number is
> required.' + CHAR(13) + CHAR(10)
>
> IF @cAddress1BT IS NULL or @cAddress1ST IS NULL
> SELECT @iRetCode = 0, @cRetMsg = @cRetMsg + 'Address 1 is required.'
> + CHAR(13) + CHAR(10)
>
> IF @cCityNameBT IS NULL or @cCityNameST IS NULL
> SELECT @iRetCode = 0, @cRetMsg = @cRetMsg + 'City Name is required.'
> + CHAR(13) + CHAR(10)
>
> IF @iZipCodeBT IS NULL or @iZipCodeST IS NULL
> SELECT @iRetCode = 0, @cRetMsg = @cRetMsg + 'Zip Code is required.' +
> CHAR(13) + CHAR(10)
>
> --If required info does not exist, exit proc
> IF @iRetCode = 0
> RETURN
>
> --Check for existence of Customer
> SELECT @iCheckCust = CustomerID FROM Customer WHERE CustName =
> @cCustName and DayPhone = @iDayPhone
>
> IF @iCheckCust IS NOT NULL
> SELECT @iRetCode = 0, @cRetMsg = @cRetMsg + 'Customer already exist.'
> + CHAR(13) + CHAR(10)
>
> --If Customer found, exit proc
> IF @iRetCode = 0
> RETURN
>
> --Insert Customer Information
> BEGIN TRAN
> INSERT INTO Customer (CustName, Reseller, ReslrNum, DayPhone,
> NightPhone, Email, CustEnterDate, OrderTaker)
> VALUES(@cCustName, @bReseller, @iReslrNum, @iDayPhone, @iNightPhone,
> @cEmail, getdate(), @cOrderTaker)
>
> -- Check if insert succeeded. If so, get CustomerID.
> IF @@ROWCOUNT = 1
> SELECT @iCustomerID = @@IDENTITY, @iRetCode = 1
> ELSE
> SELECT @iCustomerID = 0, @iRetCode = 0, @cRetMsg = 'Insertion of
> new Customer failed.'
>
> IF @iRetCode = 0
> BEGIN
> ROLLBACK TRAN
> RETURN
> END
>
> --Determine if ZipCode Exist
> IF @iZipIDBT IS NULL
> SELECT @iZipIDBT = ZipID from Zip where ZipCode = @iZipCodeBT and
> CityName = @cCityNameBT
>
> IF @iZipIDST IS NULL
> SELECT @iZipIDST = ZipID from Zip where ZipCode = @iZipCodeST and
> CityName = @cCityNameST
>
> --Insert New Bill To Zip Code
> IF @iZipIDBT IS NULL
> BEGIN
> INSERT INTO ZIP (StateID, ZipCode, CityName) VALUES
> (@iStateIDBT, @iZipCodeBT, @cCityNameBT)
>
> -- Check if insert succeeded. If so, get ZipID.
> IF @@ROWCOUNT = 1
> BEGIN
> SELECT @iZIPIDBTOut = @@IDENTITY, @iRetCode = 1
> SELECT @iZIPIDBT = @iZIPIDBTOut
> END
> ELSE
> SELECT @iZIPIDBTOut = 0, @iRetCode = 0, @cRetMsg = 'Insertion of
> new Bill To Zip Code failed.'
>
> IF @iRetCode = 0
> BEGIN
> ROLLBACK TRAN
> RETURN
> END
> End
>
> --Insert New Ship To Zip Code
> IF @iZipIDST IS NULL
> BEGIN
> INSERT INTO ZIP (StateID, ZipCode, CityName) VALUES
> (@iStateIDST, @iZipCodeST, @cCityNameST)
>
> -- Check if insert succeeded. If so, get ZipID.
> IF @@ROWCOUNT = 1
> Begin
> SELECT @iZIPIDSTOut = @@IDENTITY, @iRetCode = 1
> SELECT @iZIPIDST = @iZIPIDSTOut
> END
>
> ELSE
> SELECT @iZIPIDSTOut = 0, @iRetCode = 0, @cRetMsg = 'Insertion of
> new Ship To Zip Code failed.'
>
> IF @iRetCode = 0
> BEGIN
> ROLLBACK TRAN
> RETURN
> END
> End
>
> --Insert Customer Bill To Address
> INSERT INTO CustAddress (CustomerID, Address1, Address2,ZipID)
> VALUES (@iCustomerID, @cAddress1BT,
> @cAddress2BT, @iZipIDBT)
>
> IF @@ROWCOUNT = 1
> SELECT @iCustAddressIDBT = @@IDENTITY, @iRetCode = 1
> ELSE
> SELECT @iCustAddressIDBT = 0, @iRetCode = 0, @cRetMsg = 'Insertion
> of Bill To Customer Address failed.'
>
> IF @iRetCode = 0
> BEGIN
> ROLLBACK TRAN
> RETURN
> END
>
> --Insert Customer Ship To Address
> INSERT INTO CustAddress (CustomerID, Address1, Address2,ZipID,
> ShipTo) VALUES (@iCustomerID, @cAddress1ST,
> @cAddress2ST, @iZipIDST,1)
>
> IF @@ROWCOUNT = 1
> SELECT @iCustAddressIDST = @@IDENTITY, @iRetCode = 1
> ELSE
> SELECT @iCustAddressIDST = 0, @iRetCode = 0, @cRetMsg = 'Insertion
> of Ship To Customer Address failed.'
>
> IF @iRetCode = 0
> BEGIN
> ROLLBACK TRAN
> RETURN
> END
>
> -- Test to see if all details inserted
> IF @@ERROR = 0
> BEGIN
> COMMIT TRAN
> SELECT @iRetCode = 1, @cRetMsg = 'Customer' + @cCustName + '
> added successfully.'
> END
> ELSE
> BEGIN
> ROLLBACK TRAN
> SELECT @iCustomerID = 0, @iRetCode = 0, @cRetMsg = 'Insertion of
> new Customer failed.'
> END
>
> RETURN
>
>
> GO
| |
| Vadim Rapp 2002-10-05, 9:12 pm |
| JL> I am having trouble with a Stored Procedure I have 26 parameters
specified but, I
JL> am getting an error that too many arguments are specified. I was
executing this
JL> Stored Procedure through an ADO command object but because I could not
get it to
JL> execute I trapped the statement in SQL Profiler and tried to execute the
statement
JL> in the Query Analyzer. This is the error I receive "Server: Msg 8144,
Level 16,
JL> State 2, Procedure spCustomerInsert, Line 0 Procedure or function
spCustomerInsert
JL> has too many arguments specified." I have counted out the parameters
and I have
JL> 26, I even did the @PramaterName = "value" for each parameter and it all
worked
JL> out perfectly, except it will not execute. If anyone knows what the
problem is I
JL> would appreciate your help.
try to put RETURN in the beginning of your s.p. If the error goes, than it
was not in calling the s.p from outsite, but within the s.p. somewhere. (I
tried this, and got no error)
|
|
|
|
|