IN operator using Parameter - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

IN operator using Parameter

Author

Topic

nsap
Starting Member

1 Post

Posted - 2011-02-03 : 19:03:41

I am tring to get rows that have New South Wales or Queensland in the State column. I want to use a @State parameter and set this to different values as required.

1.) This works fine
Select State
from CheckboxV3
where isnull ( State , '' ) in ( 'New South Wales','Queensland' )
2.) This does not return anything ??? why not?
declare @State VARCHAR(100)
set @State = '''New South Wales'',''Queensland'''
Select State
from CheckboxV3
where isnull ( State , '' ) in ( @State )

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-03 : 20:33:45

The string doesn't just build itself into the SQL like that. It is different than if you only wanted 1 state

This would work:
set @State ='New South Wales'

Select State
from CheckboxV3
where State = @State

For the IN clause variable application, you need to build the whole select statement and add the variable to the string, so something like this

[/code]
SELECT @SQL = '
Select [State]
from CheckboxV3
where in ('+ @state + ' )'

sp_ExecuteSQL @SQL

[/code]

Poor planning on your part does not constitute an emergency on my part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-04 : 05:14:52

Also search for Passing Arrays+ SQL Server in google/bing

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources