SELECT where value IN @variable

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

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT where value IN @variable

Author

Topic

grimmus
Yak Posting Veteran

53 Posts

Posted - 2008-03-04 : 16:20:22

Hi,

I am writing a sproc that has simple search functionality, basically if someone selects ALL, then the sql should be the following

IF(@grade = 'ALL')BEGIN SET @gradeChoice = '1,2,3,4,5' END
ELSE BEGIN SET @gradeChoice = @grade END

Then i have

SELECT * from table where grade IN @gradeChoice

It doesnt seem to work as normal. I thought it would work the same with a variable?

Thanks for any help

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-04 : 16:27:09

it doesn't work that way to begin with, you need to use dynamic SQL, and make sure @grade is a varchar field

Declare @SQL varchar(8000)

If @Grade = 'All'
SET @Grade = '1,2,3,4,5'

SET @SQL = 'Select * from [table] where grade in (' + @Grade + ')'
Exec (@SQL)

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-04 : 16:27:34

Instead of using dynamic SQL, use a function such as this one: http://www.sqlteam.com/article/using-a-csv-with-an-in-sub-select

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-03-04 : 16:31:15

The problem is you are trying to use a sql variable like an array which it will not do. What you are actually doing is assigning the variable the string "1,2,3,4,5".

Try using something like the following instead:

SELECT  *
FROM    table
WHERE   (@grade = 'ALL'
         AND grade IN (1, 2, 3, 4, 5))
        OR (@grade <> 'ALL'
            AND grade = @gradeChoice)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-05 : 03:33:18

http://www.sommarskog.se/arrays-in-sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources