Proc has too many arguments - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Proc has too many arguments

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-20 : 09:17:52

Hi All,

I have a SP that has nearly 27 parameters. No compilation error is shown but when i execute, it gives the following error:

Msg 8144, Level 16, State 2, Procedure sp_TLCOMSCandReg, Line 0
Procedure or function sp_TLCOMSCandReg has too many arguments specified.

Please help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-20 : 09:38:12

that means you've passing more number of parameters than that's actually expected by proc. check your execute statement and make sure number of parameter values is correct

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-20 : 09:42:23

No i am passing same no of values in my exec statement. what else could be the reason?

My proc is like this:

ALTER PROCEDURE [dbo].[sp_TLCOMSCandReg] (@CandID VARCHAR(50) output,@FirstName VARCHAR(100),@MiddleName Varchar(100),
@LastName VARCHAR(100),@EmailID VARCHAR(250),@MobileNo VARCHAR (15),
@PhoneNo VARCHAR (15),@DOB DATETIME,@Gender SMALLINT,@Address Varchar(250),
@Country BIGINT,@State BIGINT,@Location BIGINT,@ResumeUpload VARCHAR(100),
@TotalExperience DECIMAL(18,2),@Industry BIGINT,@FunctionalArea BIGINT,@Working SMALLINT,
@WorkingSince DATETIME,@PresentEmployer VARCHAR(100),@PresentCTC DECIMAL(18,2),@ExpectedCTC DECIMAL(18,2),
@PreviousEmployer VARCHAR(100),@CentreID BIGINT,@CandStatus VARCHAR(20),
@CreatedUserID BIGINT,@LastModifiedUserID BIGINT)--,@InstituteName VARCHAR(100)
AS
BEGIN
BEGIN TRY
print '1'
DECLARE @ResumePrefix VARCHAR(50)
DECLARE @ResumeSuffix VARCHAR(50)
DECLARE @ResumeLastNo BIGINT
DECLARE @uniqueno varchar(50)
SELECT @uniqueno = ResumePrefix+ResumeSuffix+ResumeLastNo FROM HC_SYSCONFIG
print 'uniqueno = '+@uniqueno
UPDATE HC_SYSCONFIG SET ResumeLastNo = ResumeLastNo + 1
DECLARE @rid BIGINT
print '2'
IF @CandID IS NULL
BEGIN

INSERT INTO hc_resume_bank(UniqueNo,FirstName,MiddleName,LastName,EmailId,Mobile,PhoneH,DOB,Gender,CountryID,StateID,LocationID
,ResumeConvertedText,TotalExp,IndTypeID,FunctionID,Working,WorkingFrom,PresentEmployer,PresentAddress
,PresentCTC,ExpectedCTC,ResumeSourceID,ResumeStatus,CreatedUserID,LastModifiedUserID)
VALUES (@uniqueno,@FirstName,@MiddleName,@LastName,@EmailID,@MobileNo,@PhoneNo,@DOB,@Gender
,@Country,@State,@Location,@ResumeUpload ,@TotalExperience,@Industry,@FunctionalArea
,@Working,@WorkingSince,@PresentEmployer,@Address,@PresentCTC,@ExpectedCTC,@CentreID,@CandStatus,@CreatedUserID,@LastModifiedUserID)
SELECT @rid = max(rid) FROM hc_resume_bank where uniqueno = @CandID

INSERT INTO HC_RESUME_EMPLOYER (ResumeID,Employer,Particular)
VALUES (@rid,@PreviousEmployer,1)

INSERT INTO HC_RESUME_BANK_HISTORY(ResumeID,UserID,SourceType,Type)
VALUES (@rid,@CreatedUserID,1,0)
print 'Insert Completed'
END
ELSE
BEGIN
UPDATE HC_RESUME_BANK
SET uniqueno = @CandID, FirstName = @FirstName,LastName = @LastName,EmailID = @EmailID,Mobile = @MobileNo,
PhoneH = @PhoneNo, DOB = @DOB,Gender = @Gender,CountryID = @Country,StateID = @State,LocationID = @Location,
ResumeConvertedText = @ResumeUpload,TotalExp = @TotalExperience,IndTypeID = @Industry,FunctionID = @FunctionalArea,
WorkingFrom = @WorkingSince,PresentEmployer = @PresentEmployer,PresentCTC = @PresentCTC,ExpectedCTC = @ExpectedCTC,
ResumeStatus = @CandStatus,LastModifiedUserID = @LastModifiedUserID,DocModifiedDate = getutcdate()
print 'Update Completed'
END
END TRY
BEGIN CATCH
PRINT 'ERROR'
END CATCH
END

Sachin.Nand

2937 Posts

Posted - 2010-04-20 : 09:47:17

You need to show the execute statement.

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-20 : 09:47:49

How do you execute the procedure?
Post that code

Madhivanan

Failing to plan is Planning to fail

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-20 : 09:48:31

GO

DECLARE @return_value int,
@CandID varchar(50)

EXEC @return_value = [dbo].[sp_TLCOMSCandReg]
@CandID = @CandID OUTPUT,
@FirstName = N'a',
@MiddleName = N'b',
@LastName = N'c',
@EmailID = N'aasd',
@MobileNo = N'245657',
@PhoneNo = N'5454332',
@DOB = N'1987/09/30',
@Gender = 1,
@Address = N'dsfhff',
@Country = 12,
@State = 1,
@Location = 12,
@ResumeUpload = N'dsfdfhfh',
@TotalExperience = 2.4,
@Industry = 1,
@FunctionalArea = 1,
@Working = 1,
@WorkingSince = N'2000/01/01',
@PresentEmployer = N'hgcjhj',
@PresentCTC = 1324345,
@ExpectedCTC = 2324343,
@PreviousEmployer = N'dfgdfh',
@CentreID = 1,
@CandStatus = N'1',
@CreatedUserID = 4,
@LastModifiedUserID = 4

SELECT @CandID as N'@CandID'

SELECT 'Return Value' = @return_value

GO

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-20 : 10:09:34

EXEC @return_value = [dbo].[sp_TLCOMSCandReg]

should be

EXEC [dbo].[sp_TLCOMSCandReg]

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-20 : 10:17:10

Nope it will work provided he has a return staement whichreturns the required value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-20 : 10:24:42

quote:
Originally posted by visakh16

Nope it will work provided he has a return staement whichreturns the required value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

There is no return statement.

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources