Wednesday, November 13, 2013

TSQL - Filter/Show All or specific records from Select query

-- Trick to filter/show all or specific records from select query 
DECLARE @AllRecords NCHAR(1) 

IF (@RoleID <= 0) 
SET @AllRecords = N'Y' 
ELSE 
SET @AllRecords = N'N' 

SELECT 
ROW_NUMBER() OVER (ORDER BY ar.RoleId) AS RowID , 
RoleName, 
.... 

FROM 
table1 
... 
.. 
. 
WHERE 
ar.RoleID = @RoleID OR @AllRecords = N'Y' 
... 
.. 
. 

No comments: