CODEWITHSUNDEEP
sql-serversql-server-2008
Rob
Rob

Reputation: 1216

SELECT ROW_NUMBER() with Filters if Parameter populated?

I have the following SQL Server 2008 query. I would like to augment it so that when the @Filter parameter is populated then pass that value to each field (across columns). Without the filter the query works perfectly. I am not sure if the filters should go where I have them. Any help would be much appreciated:

ALTER PROCEDURE [dbo].[pd_Get_Team2]
@OutTotalRecCount INT OUTPUT, 
@CurrentPage INT, 
@PageSize INT, 
@Token as nvarchar(50),
@UserID as nvarchar(50),
@SortDirection INT,
@SortField nvarchar(50),
@Filter nvarchar(50)

AS     
SELECT * FROM          
(SELECT ROW_NUMBER() OVER (
ORDER BY
CASE WHEN @SortDirection = 1 AND @SortField = 1 THEN pd_Role.UserID END ASC, 
CASE WHEN @SortDirection = 2 AND @SortField = 1 THEN pd_Role.UserID END DESC
) AS Row, 
pd_Role.id,      
pd_Role.Edit_Rights, 
pd_Role.UserID, 
pd_Role.Is_Approved, 
pd_Role.Login_Date, 
pd_Role.Team  

FROM pd_Role INNER JOIN  
pd_Token ON pd_Role.Token = pd_Token.Token 
WHERE 
(pd_Token.Token = @Token) AND (pd_Token.UserID = @UserID) 

 --how do I structure a filter on these fields if the filter is populated?
 --CASE WHEN LEN(@Filter)> 1  
 --AND pd_Role.Edit_Rights LIKE '%' + @Filter + '%' OR 
 --AND pd_Role.UserID LIKE '%' + @Filter + '%' OR 
 --AND pd_Role.Team LIKE '%' + @Filter + '%' 
 --
  ) 
  AS TeamWithRowNumbers 
  WHERE  Row >= (@CurrentPage - 1) * @PageSize + 1 AND Row <= @CurrentPage*@PageSize     

  SELECT @OutTotalRecCount = COUNT(*) FROM pd_Role 
  WHERE (pd_Role.Token = @Token)

Upvotes: 1

Views: 599

Answers (2)

Tiago C&#233;sar Oliveira
Tiago C&#233;sar Oliveira

Reputation: 810

Try this:

AND ((@Filter IS NULL) OR
  (pd_Role.Edit_Rights LIKE '%' + @Filter + '%' OR 
   AND pd_Role.UserID LIKE '%' + @Filter + '%' OR 
   AND pd_Role.Team LIKE '%' + @Filter + '%')
)

Be sure that @Filter is null when not supplied, or use @Filter = '' - I like the first approach.

Upvotes: 1

Serge
Serge

Reputation: 6692

if @Filter is never NULL (but might be blank):

WHERE 
(pd_Token.Token = @Token) AND (pd_Token.UserID = @UserID) 

AND (
    pd_Role.Edit_Rights LIKE '%' + @Filter + '%' -- '%' + '' + '%' = Matches all
    OR pd_Role.UserID LIKE '%' + @Filter + '%'
    OR pd_Role.Team LIKE '%' + @Filter + '%' 
)

Upvotes: 2

Related Questions