query using the where clause if variables are not null

Question

I am trying to query without success, what I am trying to do is this:

ALTER PROCEDURE [dbo].[spPagination] -- ORDER BY id
    @filterCol NVARCHAR(20) = NULL, --<<<<
    @filterValue NVARCHAR(40) = NULL, --<<<<
    @PageNumber INT,
    @PageSize INT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT      
        Emp.id , Emp.email, Emp.[firstName], Emp.[lastName], Emp.[salary], 
        Emp.[startDateWork], Emp.age, Rol.[name] AS Role
    FROM        
        [dbo].tblEmployees5m Emp
    INNER JOIN
        [dbo].[tblRoles] Rol ON Emp.roleId = Rol.id
    WHERE       
        @filterCol LIKE '%' + @filterValue + '%' --<<<< 
    ORDER BY    
        id
        OFFSET @PageSize * (@PageNumber - 1) ROWS
        FETCH NEXT  @PageSize ROWS ONLY OPTION (RECOMPILE);

    SELECT COUNT(1) AS totalCount 
    FROM [dbo].tblEmployees5m
END

I am trying to apply filter to the result, according to @filterCol and @filterValue - if they are not NULL then I want to return the results with the where clause (which don't work now).

ELSE, if there 2 values are NULL then don't apply the where clause. @filterCol will hold the column name. @filterValue will hold the column value.

How can I achieve that? there is a better way?

I suggested to use dynamic SQL and I read about it, but I don't understand what the benefit is that I will get from using it.. is it the right way?

This is a type of catch all query. Gail Shaw blogged about it here. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ And then a follow up here. http://www.sqlservercentral.com/blogs/sqlinthewild/2018/03/13/revisiting-catch-all-queries/ — Sean Lange, Sep 05 '18 at 14:35
@TabAlleman - I don't think the duplicate you marked is the same thing. That is talking about passing a table name where this is something completely different. — Sean Lange, Sep 05 '18 at 14:42
@SeanLange, I just looked and it's talking about a column name. it even says so in the subject. I looked and found a LOT of duplicates, and did my best to pick the best one, but if you find a better one, I'll help to get the flag changed as best I can. — Tab Alleman, Sep 05 '18 at 14:44
@TabAlleman but this is not asking about passing column names as a parameter. It is about adjusting the where clause when the value of the parameter is NULL. And I mistyped table instead of column ;) — Sean Lange, Sep 05 '18 at 14:45
@SeanLange, I would say that the question could be re-written to make it about that, but the issue plaguing the posted code is the same as the issue in the flagged duplicate. When you want to use a variable for an object name in SQL Server, you have to use dynamic SQL or get creative with conditional logic, and the most-upvoted answer in the duplicate shows how. Just researching the flagged duplicate will probably lead the poster to a solution, but if not, the question could be re-worded, or a new question could be created. — Tab Alleman, Sep 05 '18 at 14:48
@TabAlleman I guess you are right. I got too focused on the catch all aspect and glossed over the column is the variable. I need more coffee..... — Sean Lange, Sep 05 '18 at 14:51
https://meta.stackoverflow.com/a/254590/6167855 with emphasis on the last part of the answer :D — S3S, Sep 05 '18 at 14:51
@scsimon With the dupe-hammer comes great responsibility. I am still coming to terms with it. Sometimes I miss the days when I was only suggesting that it was a *possible* duplicate... — Tab Alleman, Sep 05 '18 at 14:59
@TabAlleman can you not still cast a close vote instead of use the hammer? I'm not sure since I don't have it, but i notice a lot of senior members, like Gordon et al, almost never closing by themselves. I couldn't really find much on the "best practices" aside from the link above. — S3S, Sep 05 '18 at 15:01
@scsimon I really can't cast a close vote anymore without automatically closing. I suppose I could try "flagging", but that seems...not quite right. My solution so far has been, well, what I put in my long-winded reply to Sean. If the question can be edited to clearly not be a duplicate, I offer to remove the close flag. — Tab Alleman, Sep 05 '18 at 15:04
Yeah once you get the hammer there isn't a "light tap". :) Given that I missed the column as part of the parameter issue I think the duplicate is close enough. The OP needs to use that idea but also include the logic a catch-all query here. — Sean Lange, Sep 05 '18 at 15:21

Prahalad Gaggar · Accepted Answer · 2018-09-06T13:30:07.177

Read

ALTER PROCEDURE [dbo].[spPagination] -- ORDER BY id
  @filterCol NVARCHAR(20) = NULL, --<<<<
  @filterValue NVARCHAR(40) = NULL, --<<<<
  @PageNumber INT,
  @PageSize   INT
AS
BEGIN
  SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(max)
    SET @SQL='SELECT      Emp.id , Emp.email, Emp.[firstName], Emp.[lastName], Emp.[salary], Emp.[startDateWork], Emp.age  , Rol.[name] as Role
    FROM        [dbo].tblEmployees5m    Emp
    inner join  [dbo].[tblRoles]        Rol
    ON          Emp.roleId = Rol.id
    WHERE 1=1'

    IF ISNULL(@filterCol,'')!='' AND ISNULL(@filterCol,'')!=''
        SET @SQL= @SQL+' AND @fc LIKE ''%@fv%'''

    SET @SQL = @SQL+' ORDER BY    id'

    IF ISNULL(@PageNumber,'')!='' AND ISNULL(@PageSize,'')!=''
        SET @SQL= @SQL+'
        OFFSET  @PS * (@PN - 1)  ROWS
        FETCH NEXT  @PN ROWS ONLY OPTION (RECOMPILE);'




    EXEC SP_EXECUTESQL @SQL, N'@fc NVARCHAR(20) ,@fv NVARCHAR(40) ,@PN INT,@PS INT',@fc=@filterCol,@fv=@filterValue,@PG=@PageNumber,@PS=@PageSize

    select count(1) as totalCount from [dbo].tblEmployees5m
END

query using the where clause if variables are not null

1 Answers1