CODEWITHSUNDEEP
sqlsql-server-2008
PavanKumar GVVS
PavanKumar GVVS

Reputation: 1045

Sql Query Concatenation is not executing

USE [Test] Go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_ViewAdvanceSearchDetails]
( 
 @JobStatus nvarchar(20)
)
AS BEGIN

DECLARE @SelectQuery nvarchar(max)
DECLARE @JobSeekerStatus nvarchar(20)

SET @SelectQuery= N'SELECT Tbl_JobSeekers.National_Id, Tbl_JobSeekers.JobSeeker_EnglishName, Tbl_JobSeekers.JobSeeker_Arabicname, Tbl_JobSeekers.Mobile, Tbl_JobSeekers.City, 
                  Tbl_JobSeekers.Nationality, Tbl_JobSeekers.Education, Tbl_JobSeekers.Experience, Tbl_JobSeekers.Category, Tbl_JobSeekers.JobSeekerStatus, 
                  Tbl_JobSeekers.StatusEmployer, Tbl_JobSeekers.ImagePath, Tbl_JobSeekers.ResumePath, Tbl_JobSeekers.Remarks, Tbl_JobSeekers.Created_By, 
                  Tbl_JobSeekers.Created_Date
                  FROM Tbl_JobSeekers INNER JOIN
                  Tbl_City ON Tbl_JobSeekers.City = Tbl_City.City_Name INNER JOIN
                  Tbl_Qualification ON Tbl_JobSeekers.Education = Tbl_Qualification.Education_Name INNER JOIN
                  Tbl_Categories ON Tbl_JobSeekers.Category = Tbl_Categories.Category_Name INNER JOIN
                  Tbl_Experience ON Tbl_JobSeekers.Experience = Tbl_Experience.Experience_Years'

SET @JobSeekerStatus=@JobStatus

IF (@JobSeekerStatus='Selected')
    BEGIN
        SET @SelectQuery= @SelectQuery + ' WHERE Tbl_JobSeekers.JobSeekerStatus=' + @JobSeekerStatus + ' ORDER BY Tbl_JobSeekers.National_Id DESC'
    END
ELSE IF (@JobSeekerStatus='Working')
    BEGIN
        SET @SelectQuery= @SelectQuery + ' WHERE Tbl_JobSeekers.JobSeekerStatus=' + @JobSeekerStatus + ' ORDER BY Tbl_JobSeekers.National_Id DESC'
    END
ELSE IF (@JobSeekerStatus='Looking')
    BEGIN
        SET @SelectQuery= @SelectQuery + ' WHERE Tbl_JobSeekers.JobSeekerStatus=' + @JobSeekerStatus + ' ORDER BY Tbl_JobSeekers.National_Id DESC'
    END
ELSE IF (@JobSeekerStatus='All')
    BEGIN
        SET @SelectQuery= @SelectQuery + ' WHERE Tbl_JobSeekers.JobSeekerStatus=' + @JobSeekerStatus + ' ORDER BY Tbl_JobSeekers.National_Id DESC'
    END
ELSE 
    BEGIN
        SET @SelectQuery= @SelectQuery + ' ORDER BY Tbl_JobSeekers.National_Id DESC'
    END

PRINT @SelectQuery

EXEC sp_executesql @SelectQuery, N'@JobSeekerStatus nvarchar(20)', @JobSeekerStatus

END

Hi All, Based on your suggestions ,I re corrected from Exec to EXEC sp_executesql and passing parameters as value, Query is still not executing showing error message "Invalid Column 'Selected' " (which i supposed to pass as parameter value). Whatever I am passing value, its showing invalid column with that paramenter.

Upvotes: 1

Views: 77

Answers (3)

PavanKumar GVVS
PavanKumar GVVS

Reputation: 1045

Thanks for all your suggestions , Its working fine.

My Modified Code:

  SET @SelectQuery= @SelectQuery + ' WHERE Tbl_JobSeekers.JobSeekerStatus='''+ @JobSeekerStatus +''' ORDER BY Tbl_JobSeekers.National_Id DESC'

  EXEC sp_executesql @SelectQuery, N'@JobSeekerStatus nvarchar(20)',@JobSeekerStatus

Upvotes: 1

CrisisWhatCrisis
CrisisWhatCrisis

Reputation: 11

You need to wrap the @JobSeekerStatus in 3 quotes

Updated to use QUOTENAME (thanks to ughai)

E.G 

SET @SelectQuery= @SelectQuery + ' WHERE Tbl_JobSeekers.JobSeekerStatus=' + QUOTENAME(@JobSeekerStatus, '''') + ' ORDER BY Tbl_JobSeekers.National_Id DESC'

Upvotes: 1

i486
i486

Reputation: 6572

See sp_executesql stored procedure in SQL Books Online.

Upvotes: 0

Related Questions