Procedure has too many arguments

Post the code for your stored procedure and the code where you are executing it.

This is my PROC:

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

********************************************************

This is my EXEC code:

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

This doesn't have anything to do with your issue, but why are you passing nvarchar fields to a procedure that is expecting varchar?

I don't see an issue with your code.

I just ran this code, without error.

CREATE 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

select

@CandID ,@FirstName ,@MiddleName ,

@LastName ,@EmailID ,@MobileNo ,

@PhoneNo ,@DOB ,@Gender ,@Address ,

@Country ,@State ,@Location ,@ResumeUpload ,

@TotalExperience ,@Industry ,@FunctionalArea ,@Working ,

@WorkingSince ,@PresentEmployer ,@PresentCTC ,@ExpectedCTC ,

@PreviousEmployer ,@CentreID ,@CandStatus ,

@CreatedUserID ,@LastModifiedUserID

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

GO

drop proc [dbo].[sp_TLCOMSCandReg]

go

Your code has a remarked out parameter for the procedure... in the procedure you're running, are you sure that it's remarked out?

which is that nvarchar field? i didnt get 🙁

Example:

@LastName VARCHAR(100) -- defined as VARCHAR(100)

@LastName = N'c' -- value being passed in is NVARCHAR (the N in front of the 'c' indicates that this is Unicode or NVARCHAR)

Thanks for your suggestion. But when i run it , i am still getting error.

In my SP, i have given print statements at every level to check.

Now the control is going to CATCH block soon after the print message '1'.

Could you please help? its urgent.

I Get the following error: Error converting nvarchar to bigint.

Start by checking the datatypes of he parameters, the data you are passing, and the datatypes of the columns where you are sending the data.

Basic debugging 101.

SELECT @uniqueno = ResumePrefix+ResumeSuffix+ResumeLastNo FROM HC_SYSCONFIG

This statement is causing error. Convert ResumeLastNo to nvarchar

SELECT @uniqueno = ResumePrefix+ResumeSuffix+Cast(ResumeLastNo as nvarchar(100)) FROM HC_SYSCONFIG

Also recheck your code to resolve such issues.

Hello Atif,

I got where i went wrong. AS you said that ResumeLastNo was causing error.

Thanks a lot for your time and patience.

The query executes but the second and third insertions are not happening.

but it is not showing any error. Please help

Hello,

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_LINE() AS ErrorLine,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_MESSAGE() AS ErrorMessage,GETDATE(),

CONVERT(sysname ,USER_NAME())AS DBUSERNAME,

CONVERT(SYSNAME,SUSER_SNAME()) AS SYSUSERNAME

Please use the above select statement in your Catch block to catch the error.