EXECUTE sp_executesql error - Solved

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)

EXECUTE sp_executesql error - Solved

Author

Topic

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-28 : 02:24:05

Hi All,
I spent over an hour doing this but to no avail...
Please help me. In the below SP, i have included an IF cond which checks for DUPLICATES.

The EXECUTE sp_executesql statement is throwing error.
The ERROR is : @statement parameter expected.

M sorry i dont have full access to this site so could not use the font options, so not able to highlight that code.

Please help me.

ALTER PROCEDURE [dbo].[sp_TEMPTLCOMSCandReg] (@FirstName VARCHAR(100),@MiddleName Varchar(100),
@LastName VARCHAR(100),@EmailID VARCHAR(250),@MobileNo VARCHAR (15),
@PhoneNo VARCHAR (15),@DOB DATETIME,@Gender CHAR(1),@Address Varchar(250),
@Country VARCHAR(100),@State VARCHAR(100),@Location VARCHAR(100),
@TotalExperience DECIMAL(18,2),@Working CHAR(1),
@WorkingSince DATETIME,@PresentEmployer VARCHAR(100),@PresentCTC DECIMAL(18,2),@ExpectedCTC DECIMAL(18,2),
@PreviousEmployer VARCHAR(100),@CentreID BIGINT,@CandStatus VARCHAR(20),
@CreatedUserID BIGINT,@ResumeUpload IMAGE,@ResumeName NTEXT,@CandID VARCHAR(50) = NULL OUTPUT )
AS
BEGIN
BEGIN TRY
BEGIN TRAN

DECLARE @ResumePrefix VARCHAR(50)
DECLARE @ResumeSuffix VARCHAR(50)
DECLARE @ResumeLastNo BIGINT
DECLARE @uniqueno varchar(50)
DECLARE @Salutation SMALLINT
set @Salutation=1
DECLARE @Gen SMALLINT
SELECT @Gen = (CASE WHEN @Gender = 'M' THEN 0 WHEN @Gender = 'F' THEN 1 ELSE 0 END)
SELECT @Salutation = (CASE WHEN @Gender = 'M' THEN 1
WHEN @Gender = 'F' THEN 2
ELSE 1 END)
DECLARE @rid BIGINT
SET @rid=0
DECLARE @RetVal SMALLINT
SET @RetVal = 0
DECLARE @CountryID BIGINT
set @CountryID=0
SELECT @CountryID = rid FROM hcm_countries where CountryTitle = @Country
DECLARE @StateID BIGINT
set @StateID=0
SELECT @StateID = rid FROM hcm_states where StateTitle = @State
DECLARE @LocationID BIGINT
set @LocationID=0
SELECT @LocationID = rid FROM hcm_resume_locations where LocationTitle = @Location

DECLARE @Status SMALLINT
SELECT @Status = (CASE WHEN @CandStatus LIKE 'Draft' THEN 0
WHEN @CandStatus LIKE 'Available' THEN 1
WHEN @CandStatus LIKE 'In Process' THEN 2
WHEN @CandStatus LIKE 'Hired' THEN 3
WHEN @CandStatus LIKE 'Blocked' THEN 4
WHEN @CandStatus LIKE 'Not Interested' THEN 5
WHEN @CandStatus LIKE 'Interest Later' THEN 6
WHEN @CandStatus LIKE 'Not Reachable' THEN 7
WHEN @CandStatus LIKE 'On Training' THEN 11
WHEN @CandStatus LIKE 'On Training Bench' THEN 12
WHEN @CandStatus LIKE 'On Bench' THEN 13
WHEN @CandStatus LIKE 'On Assignment' THEN 14
WHEN @CandStatus LIKE 'Off Role' THEN 15
ELSE 11 END )
DECLARE @Sql VARCHAR(4000)
DECLARE @rids VARCHAR(4000)
IF @CandID IS NULL OR @CandID = ' '
BEGIN

SELECT @Sql = ' SELECT firstname,lastname,emailid,mobile FROM hc_Resume_bank
WHERE 1=1 '

IF @FirstName IS NOT NULL AND RTRIM(@FirstName) <> ' '
SET @Sql = @Sql + ' AND FirstName like '+'''' +@FirstName +''''
IF @LastName IS NOT NULL AND RTRIM(@LastName) <> ' '
SET @Sql = @Sql + ' AND LastName like '+'''' +@LastName +''''
IF @EmailID IS NOT NULL AND RTRIM(@EmailID) <> ' '
SET @Sql = @Sql + ' AND EmailID like '+'''' +@EmailID +''''
IF @MobileNo IS NOT NULL AND RTRIM(@MobileNo) <> ' '
SET @Sql = @Sql + ' AND Mobile like '+'''' +@MobileNo +''''

SET @Sql = @Sql
EXECUTE sp_executesql @Sql

IF @@ROWCOUNT <> 0

BEGIN

SELECT @uniqueno = ResumePrefix+cast(ResumeLastNo as varchar)+ResumeSuffix FROM HC_SYSCONFIG
UPDATE HC_SYSCONFIG SET ResumeLastNo = ResumeLastNo + 1

INSERT INTO hc_resume_bank(UniqueNo,FirstName,MiddleName,LastName
,EmailId,Mobile,PhoneH,DOB,Gender,Salutation,CountryID,StateID,LocationID
,TotalExp,Working,WorkingFrom,PresentEmployer,PresentAddress
,PresentCTC,ExpectedCTC,ResumeSourceID,ResumeStatus,CreatedUserID,LastModifiedUserID
)

VALUES (@UniqueNo,@FirstName,@MiddleName,@LastName
,@EmailID,@MobileNo,@PhoneNo,@DOB,@Gen,@Salutation
,CountryID,@StateID,@LocationID,@TotalExperience
,@Working,@WorkingSince,@PresentEmployer,@Address,@PresentCTC
,@ExpectedCTC,@CentreID,@Status,@CreatedUserID ,@CreatedUserID
)
SELECT @rid = max(rid) FROM hc_resume_bank where uniqueno = @uniqueno
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)

INSERT INTO HC_RESUME_ORIGINAL_DOC(ResumeID,ResumeData,ResumeFileExtension)
VALUES (@rid,@ResumeUpload,@ResumeName)

END

ELSE
BEGIN
SET @UniqueNo = '-1'
SELECT @rids = rid from hc_resume_bank
where (FirstName like @FirstName AND (@FirstName not like '' or @FirstName not like' '))
OR (LastName like @LastName AND (@LastName not like '' or @LastName not like ' '))
OR (EmailID like @EmailID AND (@EmailID not like '' or @EmailID not like ' '))
OR (Mobile like @MobileNo AND (@MobileNo not like '' or @MobileNo not like ' '))

END
END

SET @CandID = @UniqueNo
COMMIT TRAN

END TRY
BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
SELECT @ErrorMessage = ERROR_MESSAGE()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()

IF @@TRANCOUNT > 0
ROLLBACK TRAN
RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState )

END CATCH
END

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2010-04-28 : 02:44:52

I guess
instead of using dynamic sql for this you can go in this way

SELECT firstname,lastname,emailid,mobile FROM hc_Resume_bank
where (isnull(@FirstName,'') ='' or FirstName like @FirstName)
and (isnull(@LastName,'') ='' or LastName like @LastName)
and (isnull(@EmailID,'') ='' or EmailID like @EmailID)
and (isnull(@Mobile,'') ='' or Mobile like @Mobile)

Karthik
http://karthik4identity.blogspot.com/

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-28 : 02:54:41

I use to do the same but the requirement changed. so the dynamic stuff.
Thanks.

Now this is partly resolved, i need to return the IDs if i find any duplicate rows. For that i have one more o/p parameter @ID . This should return the primary key of the existing record in case of duplicate.

mrm23
Posting Yak Master

198 Posts

Posted - 2010-04-28 : 03:27:44

Hi All,
Thanks for youe time and support. I could resolve it.
All i had to do was include 'N' before the dynamic query and declare @Sql as NVARCHAR.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources