SQL IN operator

boxhead · March 30, 2011, 11:17am

I want to pass a parameter a string link this:

‘3,4,5’

I want to then use the IN operator to search for any of these number in an ID field which is an INT.



@idsString nvarchar(MAX)

SELECT	*
FROM	MyTable
WHERE
MyTable.IntID IN (idsString)

when I pass the string I get the error:

Msg 245, Level 16, State 1, Procedure pMyProc, Line 30
Conversion failed when converting the nvarchar value ‘3,4,5’ to data type int.

The second issue is that this string may also be empty so I want to do :



@idsString nvarchar(MAX)

SELECT	*
FROM	MyTable
WHERE
MyTable.IntID IN ISNULL((idsString), MyTable.IntID)

but this won’t even compile.

Any help/advice?

cheers

r937 · March 30, 2011, 11:35am

you need to use dynamic sql

construct the sql string like this (caution: this is pseudocode) –

@sql = 'SELECT * FROM MyTable WHERE MyTable.IntID IN ( '
@sql = @sql + @idsString 
@sql = @sql + ' )'

EXEC ( @sql )

system · March 30, 2011, 12:41pm

I’ve done the following in the past without Dynamic SQL by implementing a SPLIT UDF and then doing something like:


SELECT *
FROM whatever
WHERE id IN SPLIT(@idsString);

In my opinion, it’s better than Rudy’s suggestion, because it’s not interpreted. That makes it more secure (no need to worry about SQL injection at the DB layer), better performing because the query is compiled on creation of the procedure (so utilizes MS-SQL’s full procedure optimization), easier to debug, and easier to maintain long term.

Of course, Rudy’s example doesn’t require the creation of a UDF first…

Cheers.

boxhead · March 30, 2011, 2:29pm

transio:

I’ve done the following in the past without Dynamic SQL by implementing a SPLIT UDF and then doing something like:
SELECT *
FROM whatever
WHERE id IN SPLIT(@idsString);
In my opinion, it’s better than Rudy’s suggestion, because it’s not interpreted. That makes it more secure (no need to worry about SQL injection at the DB layer), better performing because the query is compiled on creation of the procedure (so utilizes MS-SQL’s full procedure optimization), easier to debug, and easier to maintain long term.

Of course, Rudy’s example doesn’t require the creation of a UDF first…

Cheers.

this seems lke the way to go for me

Right i have:


SELECT	*
FROM	MyTable
WHERE	MyTable.intID IN (Select(dbo.SPLIT(@idsString)))

but i get this error:
Cannot find either column “dbo” or the user-defined function or aggregate “dbo.SPLIT”, or the name is ambiguous.

system · March 30, 2011, 2:31pm

I don’t remember the exact implementation… as far as I recall, SPLIT returns a resultset, so you should be able to do something like “SELECT * FROM dbo.SPLIT(@idsString);”

Try to get that working by itself first and then worry about putting it in an “IN” statement.

boxhead · March 30, 2011, 2:40pm

ok - so this work fine:


declare @idsString nvarchar(max)
set @idsString = N'2,3,4'
SELECT * FROM dbo.SPLIT(@idsString,1,0)

returning
2
3
4

but this still fails:


declare @idsString nvarchar(max)
set @idsString = N'2,3,4'
SELECT	*
FROM	mytable
WHERE	mytable.intID IN (dbo.SPLIT(@idsString,1,0))

system · March 30, 2011, 2:44pm

Try this:

SELECT *
FROM mytable
WHERE mytable.intID IN (SELECT * FROM dbo.SPLIT(@idsString,1,0))

boxhead · March 30, 2011, 3:16pm

Top job! realised my initial mistake now! missed the * FROM !!!

Thanks for all your help - great function (and you Rudy :))!

monkey

system · March 30, 2011, 3:34pm

Cheers.