Stored procedure dynamic query with if

Question

I have a stored procedure in which I have to compare the values using =, <, > as a parameter and compare it with the another parameter value rating which has the value 1,2,3,4,5 but not have any idea how to do that please help.

Here is my stored procedure what I have tried so far, and I have three tables user, RatingReview and accounttype :

Create Procedure searchsp_LenderSearch
    @pAccountTypeId int =null,
    @pFirstName varchar(25)=null,
    @pLastName varchar(35)=null,
    @pZip varchar(10)=null,
    @pOperator varchar(2)=null,
    @pRating varchar(2)=null
AS
BEGIN

    SET NOCOUNT ON;
Declare @SQLQuery AS NVarchar(4000)
    SET @SQLQuery=
  'select 
   at.AccountName,
   U.UserId,
   U.FirstName,
   U.LastName,
   U.NMLS,
   U.[Address],
   U.PrimaryEmailId As Office,
   U.AdditionalEmail As Personal,
   U.DirectPhone As Work,
   U.Mobile,
   R.RatingStar

    from [User] U
    left outer join  RatingReview R   on R.UserId =U.UserId
    left outer join AccountType at on at.AccountTypeId = U.AccountTypeId
  where U.Deleted =0
  AND at.AccountTypeId = '+CAST(@pAccountTypeId as Varchar(10))+'
  AND U.FirstName = ''' + @pFirstName + '''
  AND U.LastName = ''' + @pLastName + '''
  AND U.Zip = ''' + @pZip + '''
  AND R.RatingStar = ''' + @pRating  + ''''


 IF(@pAccountTypeId  !=null OR @pAccountTypeId  != '')

    BEGIN
        SET @SQLQuery=@SQLQuery+' AND at.AccountTypeId='+CONVERT(VARCHAR, @pAccountTypeId )
    END

   IF(@pFirstName !=null OR @pFirstName != '')
    BEGIN
        SET @SQLQuery=@SQLQuery+' AND U.FirstName Like ''%' + @pFirstName + '%'''
    END

    IF(@pLastName !=null OR @pLastName != '')
    BEGIN
        SET @SQLQuery=@SQLQuery+' AND U.LastName Like ''%' + @pLastName + '%'''
    END

    IF(@pZip !=null OR @pZip != '')
    BEGIN
        SET @SQLQuery=@SQLQuery+' AND U.Zip Like ''%' + @pZip + '%'''
    END

    IF(@pRating !=null OR @pRating != '')
    BEGIN
        SET @SQLQuery=@SQLQuery+' AND R.RatingStar Like ''%' + @pRating + '%'''
    END

   IF(@pOperator = '=')
    BEGIN
    SET @SQLQuery += ' AND R.RatingStar = ' + CAST(@pRating AS NVARCHAR(5)) + ''
    END

    IF(@pOperator = '>')
        BEGIN
        SET @SQLQuery += ' AND R.RatingStar > ' + CAST(@pRating AS NVARCHAR(5)) + ''
        END

    IF(@pOperator = '<')
        BEGIN
        SET @SQLQuery += ' AND R.RatingStar < ' + CAST(@pRating AS NVARCHAR(5)) + ''
        END
        SET @SQLQuery=@SQLQuery+
  'group by 
  at.AccountName,
  U.UserId,
   U.FirstName,
   U.LastName,
   U.NMLS,
   U.[Address],
   U.PrimaryEmailId,
   U.AdditionalEmail,
   U.DirectPhone,
   U.Mobile,
   R.RatingStar'

   EXEC (@SQLQuery) 
END

Answer 1

You need to escape the parameters you pass into the query to concatenate them in the @sqlstring variable. Try this query:

Create PROCEDURE searchsp_LenderSearch
    @pAccountType int,
    @pFirstName varchar(25),
    @pLastName varchar(35),
    @pZip varchar(10),
    @pOperator varchar(2),
    @pRating int 
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQLQuery AS NVarchar(4000)
    SET @SQLQuery = '
SELECT 
    at.AccountTypeId,
    U.UserId,
    U.FirstName,
    U.LastName,
    R.RatingStar AS Rating,
    U.NMLS,
    U.[Address],
    U.PrimaryEmailId As Office,
    U.AdditionalEmail As Personal,
    U.DirectPhone As Work,
    U.Mobile
FROM [User] U
LEFT OUTER JOIN RatingReview R ON R.UserId = U.UserId
LEFT OUTER JOIN AccountType at ON at.AccountTypeId = U.AccountTypeId
WHERE U.Deleted = 0
  AND at.AccountTypeId = 1
  AND U.FirstName = ''' + @pFirstName + '''
  AND U.LastName = ''' + @pLastName + '''
  AND U.Zip = ''' + @pZip + ''''

IF(@pOperator = '=')
    BEGIN
    SET @SQLQuery += ' AND r.ratingstar = ' + CAST(@pRating AS NVARCHAR(5)) + ''
    END
IF(@pOperator = '<')
    BEGIN
    SET @SQLQuery += ' AND r.ratingstar < ' + CAST(@pRating AS NVARCHAR(5)) + ''
    END
IF(@pOperator = '>')
    BEGIN
    SET @SQLQuery += ' AND r.ratingstar > ' + CAST(@pRating AS NVARCHAR(5)) + ''
    END

SET @SQLQuery += '
GROUP BY 
    at.AccountTypeId,
    U.UserId,
    U.FirstName,
    U.LastName,
    U.NMLS,
    U.[Address],
    U.PrimaryEmailId,
    U.AdditionalEmail,
    U.DirectPhone,
    U.Mobile,
    R.RatingStar'
EXEC (@SQLQuery) 
END

The code above will generate a query like:

SELECT 
    at.AccountTypeId,
    U.UserId,
    U.FirstName,
    U.LastName,
    R.RatingStar AS Rating,
    U.NMLS,
    U.[Address],
    U.PrimaryEmailId As Office,
    U.AdditionalEmail As Personal,
    U.DirectPhone As Work,
    U.Mobile
FROM [User] U
LEFT OUTER JOIN RatingReview R ON R.UserId = U.UserId
LEFT OUTER JOIN AccountType at ON at.AccountTypeId = U.AccountTypeId
WHERE U.Deleted = 0
  AND at.AccountTypeId = 1
  AND U.FirstName = 'John'
  AND U.LastName = 'Adams'
  AND U.Zip = '100AA' AND r.ratingstar > 1
GROUP BY 
    at.AccountTypeId,
    U.UserId,
    U.FirstName,
    U.LastName,
    U.NMLS,
    U.[Address],
    U.PrimaryEmailId,
    U.AdditionalEmail,
    U.DirectPhone,
    U.Mobile,
    R.RatingStar

if executed like exec searchsp_LenderSearch 1, 'John', 'Adams', '100AA', '>',1

I think this is what you want. Obviously you might want to add some error checking and validation of parameters etc.

As requested here is an updated version of the query changed to match the updated question:

Create Procedure searchsp_LenderSearch
    @pAccountTypeId int       = null,
    @pFirstName varchar(25) = null,
    @pLastName      varchar(35) = null,
    @pZip           varchar(10) = null,
    @pOperator      varchar(2)  = null,
    @pRating        varchar(2)  = null
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQLQuery AS NVarchar(4000)
    SET @SQLQuery ='
SELECT 
    at.AccountName,
    U.UserId,
    U.FirstName,
    U.LastName,
    U.NMLS,
    U.[Address],
    U.PrimaryEmailId As Office,
    U.AdditionalEmail As Personal,
    U.DirectPhone As Work,
    U.Mobile,
    R.RatingStar
FROM [User] U
LEFT OUTER JOIN RatingReview R ON R.UserId = U.UserId
LEFT OUTER JOIN AccountType at ON at.AccountTypeId = U.AccountTypeId
WHERE U.Deleted = 0
 '
IF(@pAccountTypeId  !=null OR @pAccountTypeId  != '')
    BEGIN
        SET @SQLQuery += ' AND at.AccountTypeId = '+CONVERT(VARCHAR(10), @pAccountTypeId ) + '' 
    END

IF(@pFirstName !=null OR @pFirstName != '')
    BEGIN
        SET @SQLQuery += ' AND U.FirstName Like ''%' + @pFirstName + '%'''
    END

IF(@pLastName !=null OR @pLastName != '')
    BEGIN
        SET @SQLQuery += ' AND U.LastName Like ''%' + @pLastName + '%'''
    END

IF(@pZip !=null OR @pZip != '')
    BEGIN
        SET @SQLQuery += ' AND U.Zip Like ''%' + @pZip + '%'''
    END

IF(@pOperator = '=')
    BEGIN
        SET @SQLQuery += ' AND R.RatingStar = ' + CAST(@pRating AS NVARCHAR(5)) + ''
    END

IF(@pOperator = '>')
    BEGIN
        SET @SQLQuery += ' AND R.RatingStar > ' + CAST(@pRating AS NVARCHAR(5)) + ''
    END

IF(@pOperator = '<')
    BEGIN
        SET @SQLQuery += ' AND R.RatingStar < ' + CAST(@pRating AS NVARCHAR(5)) + ''
    END

SET @SQLQuery += '
GROUP BY 
    at.AccountName,
    U.UserId,
    U.FirstName,
    U.LastName,
    U.NMLS,
    U.[Address],
    U.PrimaryEmailId,
    U.AdditionalEmail,
    U.DirectPhone,
    U.Mobile,
    R.RatingStar'

    EXEC (@SQLQuery) 
   --PRINT (@SQLQuery) 
END