Multiple Conditionals

Peter8675309

I am writing a stored procedure where the WHERE clause may contain 100's of conditionals (i.e. UserID=1 OR UserID=2, etc). The field being searched will always be the same. It is just the number records to be returned I won't about. Is there anyways to pass the stored procedure an array or something similar to specify the records to return? Writing a statement that includes 100's of "UserID=1 OR UserID=2 OR..." breaks my layer abstraction so I'd rather just send the list of User ID's I want to get. Is there any way to do something like this?:wtf:

Mike Ellison

Can you pass a comma-delimited string of values as a single varchar parameter? Then use that parameter as the list in an IN(...) operand?

EdbertP

You can try passing a string with the user id's and use it in the WHERE clause of your statement, e.g.

SELECT *
FROM tblUsers
WHERE UserID IN (SELECT [Value] FROM dbo.Split(@ListOfUserIDs,','))

The code above uses the user defined function Split, which splits a string by the delimiter and converts it into a table. Below is the user defined function that you can modify as needed.

CREATE FUNCTION dbo.Split
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(

Id int identity(1,1),
Value nvarchar(100)

)
AS
BEGIN

WHILE (CHARINDEX(@SplitOn,@List)>0)
BEGIN 

INSERT INTO @RtnValue (value)
SELECT 
    Value = LTRIM(RTRIM(SUBSTRING(@List,1,CHARINDEX(@SplitOn,@List)-1))) 

   Set @List = SUBSTRING(@List,CHARINDEX(@SplitOn,@List)+LEN(@SplitOn),LEN(@List))
END

INSERT INTO @RtnValue (Value)
SELECT Value = LTRIM(RTRIM(@List))

RETURN

END

There is another way using the EXEC statement in your stored procedure to execute the SQL, and you don't need the split function for this. Some users argued about the efficiency and safety of using EXEC statement, but it's up to you to decide.

DECLARE SqlStr varchar(1000)

SET SqlStr = 'SELECT * FROM tblUser WHERE UserID IN (' + @ListOfUserID + ')'
EXEC (SqlStr)

Both code will work even if you only pass one ID. I hope it helps ;).

Peter8675309

Thanks for the function. It works great.