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 that

  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?

Answer 1

Here's the code:

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
    '
    IF @filterCol IS NOT NULL
    BEGIN
        SET @SQL = @SQL + 'WHERE       [' + @filterCol + '] LIKE ''%' + @filterValue + '%'''
    END

    SET @SQL = @SQL + '
    ORDER BY    id
    OFFSET      ' + CAST((@PageSize * (@PageNumber - 1)) AS VARCHAR(50)) + ' ROWS
    FETCH NEXT  ' + CAST(@PageSize AS VARCHAR(25)) + ' ROWS ONLY OPTION (RECOMPILE);'

    EXEC sp_executesql @SQL

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

As has been mentioned, dynamic SQL is vulnerable to SQL Injection, so take care when utilising dynamic SQL that you're sufficiently protected against these attacks.

SQL Injection Protection: https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet