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
@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?