SQL IN operator

[boxhead]

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]

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 )

[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.

[boxhead]

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.

[transio]

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]

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.

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))

[transio]

Try this:

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

[boxhead]

Try this:

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

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

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

monkey

[transio]

Cheers.