Where In () values generated dynamically

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

Where In () values generated dynamically

Author

Topic

probelaw
Starting Member

2 Posts

Posted - 2009-12-28 : 01:29:51

I need to design a query using where in () to select from a list of multiple values...

SELECT EmployeeID, EmployeeName from EMPLOYEES WHERE OFFICE_ID in (1,5,7)

simple enough.

However, the list of OFFICE_IDs in the where clause needs to be generated dynamically in my asp script.

Can't seem to get this to work using Createparameter. The OFFICE_ID values are int. How can I create a parameter of multiple int values?

Any help would be appreciated.

vikky
Yak Posting Veteran

54 Posts

Posted - 2009-12-28 : 01:49:59

HI,

try this one

SELECT EmployeeID, EmployeeName from EMPLOYEES WHERE ','+@generatedvalues+',' LIKE '%,'+ CAST(OFFICE_ID AS VARCHAR(20))+',%'

Thanks,
vikky.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-28 : 01:50:31

is this u want..........
declare @i varchar(max)

select @i = '1,2,5'

select empid,sum(empsal) from emptable where '%,' + @i+ ',%' LIKE '%,' + CAST( empid AS VARCHAR(255)) +',%'
group by empid

exec('select empid,sum(empsal) from emptable where cast(empid as varchar) in('+@i+') group by empid')

select * from emptable where patindex('%,' + CAST( empid AS VARCHAR(255)) +',%',','+@i+',' )>0

probelaw
Starting Member

2 Posts

Posted - 2009-12-28 : 12:01:01

So, If I understand correctly, the final query executed on the db should be:

SELECT EmployeeID, EmployeeName FROM Employees
WHERE '%,' + 1,2,3 + ',%' LIKE '%,' + CAST( OFFICE_ID AS VARCHAR(255)) +',%'

When I try to run this, it returns "an expression of non-boolean type specified in a context where a condition is expected.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-12-28 : 12:10:39

quote:
Originally posted by probelaw

So, If I understand correctly, the final query executed on the db should be:

SELECT EmployeeID, EmployeeName FROM Employees
WHERE '%,' + '1,2,3' + ',%' LIKE '%,' + CAST( OFFICE_ID AS VARCHAR(255)) +',%'

When I try to run this, it returns "an expression of non-boolean type specified in a context where a condition is expected.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources