Hi all,

Had a sql question for you SQL folks out there... using sqlserver:

DECLARE @MyVar varchar
SET @MyVar = "('one', 'two')"

I'm able to do this:

SELECT * FROM Table WHERE field = @MyVar

...but not this:

SELECT * FROM Table WHERE field IN @MyVar

Is there a way to do the 2nd SELECT without building up a string and running it via EXEC(), or is that the way it has to be done?

Thanks!

Is this within a procedure, or just in T-SQL?

Right now, in the editor... but eventually it's going to be in a SP.

The way that I've done this before (variable-length IN clauses) at least in Oracle, is by creating a 'helper' function that will return (or populate) a temporary table, and parse apart the string by commas (a la the perl 'split' function). Then the select would look something like:

select * from table where field in (select * from myfunc ( @MyVar ) );

Or, in this SP you're writing you could parse apart the contents of @MyVar and construct the in clause yourself.