sp using "in" clause - 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

SQL Server 2000 Forums

SQL Server Development (2000)

sp using "in" clause

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-15 : 08:24:10

Michael writes "I have 3 tables, jobs, job_locations and jobs_job_locations lookup table.


Jobs		job_locations	jobs_job_locations
job_id		job_location_id	job_id
description	description	job_location_id

so that in jobs_job_locations I can have a job with multi job_locations.

so my select would be

CREATE procedure FindJob
@job_locsvarchar(50)
as
select * from jobs a, job_locations b, jobs_job_locations c where
a.job_id=c.job_id and b.job_location_id=c.job_location_id and
c.job_location_id in (@job_locs)
go

if I run exec findjob '1' it works, but when I run
findjob '1,2' it bombs out since it will try to convert '1,2' into a integer. What can I do to make this work? DB engine is MS SQL 2000.

thanks
mikec"

dsdeming

479 Posts

Posted - 2003-07-15 : 08:34:34

I've always had better luck with CHARINDEX. You could try something like this:

CREATE PROCEDURE FindJob
@job_locs varchar(50)
AS

SET @job_locs = ',' + @job_locs + ','

SELECT *
FROM jobs a,
JOIN jobs_job_locations c ON a.job_id=c.job_id
JOIN job_locations b ON b.job_location_id=c.job_location_id
WHERE CHARINDEX( ',' + CAST( c.job_location_id AS varchar( 12 )) + ',', @job_locs ) > 0
go

Dennis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources