SqlServer question...

[ajf3]

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!

 

[shutterFly]

Try this:

DECLARE @MyVar varchar
SET @MyVar = ',one,two,'

SELECT * FROM Table WHERE CHARINDEX(',' + field + ',', @MyVar) > 0

 

[kamper]

Hmm, I only know a little transact-sql but I could take a stab at it.

In your second example you're trying to do an IN on something that is not a tuple of any sort. What I think you're assuming is that the contents of @MyVar will get inserted into the sql statement as a string, which they won't. transact-sql is smarter than that.

Some options would be to actually make @MyVar a complex data type like some sort of result set, or to manually generate your sql string and then execute it like so:

@sql_string = 'select * from table where field in ' + @MyVar
exec_sql(@sql_string)

Or you could do what I think shutterFly is showing and parse your potential values out of @MyVar

 

[ajf3]

Thx guys -

Right, I knew I could go about it with an exec, but wanted to see if there was another way to make sqlserver recognize @MyVar as part of the statement... I tried the charindex example - it didn't error, but didn't return any rows either...

 

[kamper]

You should have a look through the manual and see if there are any complex data types that might work for this.

 

[ajf3]

Thing is... I don't think I'd be able to pass that into a sp, would I?

 

[MrChad]

Are the single quotes in @MyVar properly escaped? What happens if you run "print @MyVar"?

 

[ajf3]

Actually, a Print @MyVar just returns the 1st character of the string -ie, (

 

[torpid]

This is not possible in SQL server or probably any other RDBMS so easily. I am positive there is an article somewhere explaining a more complicated solution to this, but I couldn't find it easily with a google.

 

[shutterFly]

Hmm... from these replies, I can see what might be the problem.

First, declare @MyVar as a VARCHAR(200) or something. Looks like you're only declaring it as a VARCHAR with no length. This will default it to a length of 1 as indicated by your PRINT attempt. Sorry, I should have been more clear about that.

Second, what was @MyVar when you tried CHARINDEX? Make sure you drop the parentheses and don't use double quotes. Also, make sure there are no spaces when delimiting the values.

Should look like: ',one,two,three,'

I know what you mean by not wanting to use an EXEC command on a string. It sort of defeats the purpose of having a pre-compiled stored procedure.

Good Luck!

 

[MrChad]

Originally posted by: ajf3
Actually, a Print @MyVar just returns the 1st character of the string -ie, (

Sounds like your single quotes are not escaped.

SET @MyVar = '(''one'',''two'')'

 

[KentState]

Why don't you just pass a recordset into the stored procedure and use that to join to the other table?

 

[ajf3]

Hey - it works

Changing varchar to varchar(200) allowed the charindex example to return the expected results - thanks!

 

[ajf3]

Why don't you just pass a recordset into the stored procedure and use that to join to the other table?

Didn't realize that was possible... I'll have to look into it.