CODEWITHSUNDEEP
sqlsql-server
Azz Falconer
Azz Falconer

Reputation: 44

How to carry out 'IN' and 'WHERE' clause in single query

I have a SQL Server select statement which is supposed to work on case basis

Case 1: When user role id supplied is admin all data will be select

Case 2: Apart form admin role the select will return all data except admin data.

DECLARE @UserRoleID INT = 3
SELECT urp.id AS 'iD'
    ,canview AS 'read'
    ,cancreate AS 'create'
    ,canedit AS 'update'
    ,candelete AS 'delete'
    ,ur.description AS 'userRole'
    ,ch.description AS 'rightsToMenu'
    ,urp.creator AS 'creator'
    ,urp.datecreated AS 'dateCreated'
    ,urp.datemodified AS 'dateModified'
    ,urp.modifier AS 'modifier'
FROM userrolepermission urp
INNER JOIN userrole ur ON urp.userroleid = ur.id
INNER JOIN channel AS ch ON urp.channelid = ch.id
WHERE urp.UserRoleID IN (
        (
            CASE 
                WHEN @UserRoleID = 1
                    THEN ('2')
                ELSE CAST(@UserRoleID AS NVARCHAR)
                END
            -- tried this also
            WHERE (
                    CASE 
                        WHEN urp.userroleid = @UserRoleID
                            AND @UserRoleID > 1
                            THEN @UserRoleID
                        ELSE @UserRoleID
                        END IN (
                        SELECT ID
                        FROM UserRole(NOLOCK)
                        )
                    )
            )
        )

Here where condition returns all data which is not desired.

Example:

@UserRoleID = 1 - Then Retrieve all data and If @UserRoleID != 1 Then Retrieve specific matching records.

Upvotes: 0

Views: 111

Answers (5)

Its_Ady
Its_Ady

Reputation: 336

You can simply use Dynamic SQL.

DECLARE @query NVARCHAR(MAX)
  SET @query='SELECT urp.id AS [iD]
,canview AS [read]
,cancreate AS [create]
,canedit AS [update]
,candelete AS [delete]
,ur.description AS [userRole]
,ch.description AS [rightsToMenu]
,urp.creator AS [creator]
,urp.datecreated AS [dateCreated]
,urp.datemodified AS [dateModified]
,urp.modifier AS [modifier]
     FROM userrolepermission urp
     INNER JOIN userrole ur ON urp.userroleid = ur.id
   INNER JOIN channel AS ch ON urp.channelid = ch.id '


       IF @UserRoleID=1
    BEGIN

      SET @query += ' your condition'
       END 
     ELSE 

     BEGIN 

     SET @query += ' your condition'
     end


   EXECUTE @query

Upvotes: 2

Malakiya sanjay
Malakiya sanjay

Reputation: 208

used this Code...

 WHERE urp.UserRoleID IN((CASE WHEN @UserRoleID = 1 THEN ('2') ELSE CAST(@UserRoleID AS NVARCHAR) END))

UserRoleID=3 record get it this code usefully

Upvotes: 0

Venkataraman R
Venkataraman R

Reputation: 12959

You can go for IF ELSE logic. Eventhough it looks longer, it looks cleaner.

DECLARE @UserRoleID INT =3

IF @UserRoleID = 1 -- admin
BEGIN

   SELECT    
                          urp.id             AS 'iD',   
                          canview            AS 'read',   
                          cancreate          AS 'create',   
                          canedit            AS 'update',   
                          candelete          AS 'delete',   
                          ur.description     AS 'userRole',   
                          ch.description     AS 'rightsToMenu',   
                          urp.creator        AS 'creator',   
                          urp.datecreated    AS 'dateCreated',   
                          urp.datemodified   AS 'dateModified',   
                          urp.modifier       AS 'modifier'   
               FROM       userrolepermission AS urp   
               INNER JOIN userrole           AS ur   
               ON         urp.userroleid = ur.id   
               INNER JOIN channel AS ch   
               ON         urp.channelid = ch.id 
END
ELSE
BEGIN

     SELECT    
                          urp.id             AS 'iD',   
                          canview            AS 'read',   
                          cancreate          AS 'create',   
                          canedit            AS 'update',   
                          candelete          AS 'delete',   
                          ur.description     AS 'userRole',   
                          ch.description     AS 'rightsToMenu',   
                          urp.creator        AS 'creator',   
                          urp.datecreated    AS 'dateCreated',   
                          urp.datemodified   AS 'dateModified',   
                          urp.modifier       AS 'modifier'   
               FROM       userrolepermission AS urp   
               INNER JOIN userrole           AS ur   
               ON         urp.userroleid = ur.id   
               INNER JOIN channel AS ch   
               ON         urp.channelid = ch.id 
           WHERE urp.UserRoleID = @UserRoleID 

END

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use Boolean logic instead : 

where (@UserRoleID = 1) OR
      (@UserRoleID <> 1 AND UserRole = @UserRoleID);

Upvotes: 1

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try like below 

WHERE (
        isnull(@UserRoleID, 0) = 1
        OR (
            isnull(@UserRoleID, 0) <> 1
            AND dbo.TableName.UserRole = @UserRoleID
            )
        )

Upvotes: 0

Related Questions