Using Parameter variable for IN Clause values

Question

I have the user role parameter as this

declare @UserRoles varchar (200)
set @UserRoles = '1,2,3'

I want to check in the where clause something like this

SELECT * FROM Request where Role IN (@UserRole)

Role column is an integer and this causes an error since @UserRole is considered as string rather than a bunch of comma delimited integers. Is there anyway to do this?

UPDATE:

The reason why i need to do this is because each user has multiple roles. And he needs to view records according to his role and status. I need to be able to create a query that looks something like this.

( 
   -- Get All Request Created By User
   (r.RequestedBy=@Username) 
   OR
   -- Get All Request Assigned To Role1
   (5 in (@UserRoles) and rs.StatusID IN (1))
   OR
   -- Get All Request Assigned To Role2
   (6 in (@UserRoles) and rs.StatusID IN (2))
   OR
   -- Get All Request Assigned To Role3
   (1 in (@UserRoles) and rs.StatusID IN (7,8))
  )

What wrong with normal table - row approach for roles or rights? What if you will have more then 100 roles? — Fabio, Oct 13 '15 at 10:14
Probably a bad choice to begin with. can you explain why you'd want to do this? — Amit, Oct 13 '15 at 10:16
@Amit this is a simplified version of what i want to achieve. I will update the question to further explain my problem. — TheProvost, Oct 13 '15 at 10:35
@Fabio updated my question. User role is static and is only 6 types — TheProvost, Oct 13 '15 at 10:39

score 0 · Answer 1 · answered Oct 13 '15 at 14:23

So a table valued function could help to split the string of roles into rows, then you could do a select, something like this:

CREATE FUNCTION [dbo].[SplitString] ( @string NVARCHAR(MAX) )
RETURNS @retVal TABLE ( val NVARCHAR(MAX) )
 AS
        BEGIN

            INSERT  INTO @retVal
                    ( val
                    )
                    SELECT  split.a.value('.', 'VARCHAR(MAX)') AS String
                    FROM    ( SELECT    CAST ('<M>' + REPLACE(@string, ',',
                                                              '</M><M>')
                                        + '</M>' AS XML) AS string
                            ) AS A
                            CROSS APPLY String.nodes('/M') AS split ( a );

            RETURN;
        END;
GO

Then something like this would work:

DECLARE @values TABLE
    (
      [request] NVARCHAR(MAX) ,
      [role] NVARCHAR(MAX)
    );

INSERT  INTO @values
        ( [request], [role] )
VALUES  ( N'A', N'1' ),
        ( N'B', N'2' ),
        ( N'C', N'2' ),
        ( N'D', N'4' ),
        ( N'E', N'5' ),
        ( N'F', N'6' );

SELECT  *
FROM    @values
WHERE   [role] IN ( SELECT  *
                    FROM    dbo.SplitString('2,3,4') );

If you passed the string '2,3,4' for example, it would return the values from the table:

request | role

B | 2

C | 2

D | 4

Using Parameter variable for IN Clause values

1 Answers1