Build WHERE 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

General SQL Server Forums

New to SQL Server Programming

Build WHERE clause

Author

Topic

Access
Starting Member

44 Posts

Posted - 2008-04-08 : 23:42:07

I have a stored procedure which expects one parameter @City

Let’s say @City holds pipe delimited value: "New York|London|Paris"

In my stored procedure I need to replace all pipes with OR operator? In other words my select statement needs to look like this :

SELECT * FROM City WHERE CityName= 'New York' OR CityName= 'London' OR CityName= 'Paris'

Thank you

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-09 : 00:29:19

DECLARE @city VARCHAR(100)
DECLARE @where VARCHAR(100)
DECLARE @Select VARCHAR(500)
SET @city='New York|London|Bangalore'

SET @Select='SELECT * FROM City WHERE CityName in'
SET @where='('+''''+replace(@city,'|',''',''')+''''+')'
SET @Select=@Select+@where
Exec(@Select)

Note: "In my stored procedure I need to replace all pipes with OR operator?" - Since you have multiple values being checked against the same column, better to use IN!

Prakash.P
The secret to creativity is knowing how to hide your sources!

raky
Aged Yak Warrior

767 Posts

Posted - 2008-04-09 : 00:52:40

DECLARE @city TABLE (id INT IDENTITY(1,1), cname VARCHAR(40), state VARCHAR(30))
INSERT INTO @city VALUES('vij','AP')
INSERT INTO @city VALUES('hyd','AP')
INSERT INTO @city VALUES('bgl','KARNATAKA')
INSERT INTO @city VALUES('chennai','TAMILNADU')
INSERT INTO @city VALUES('kolkota','WESTBENGAL')
INSERT INTO @city VALUES('pune','MAHARASTRA')

DECLARE @search VARCHAR(800)
SET @search = 'bgl|kolkota|vij|pune'

SELECT
id,
cname,
state
FROM
@city
WHERE
'|'+@search+'|' LIKE '%|'+cname+'|%'

Access
Starting Member

44 Posts

Posted - 2008-04-09 : 00:58:19

quote:
Originally posted by pravin14u

DECLARE @city VARCHAR(100)
DECLARE @where VARCHAR(100)
DECLARE @Select VARCHAR(500)
SET @city='New York|London|Bangalore'

SET @Select='SELECT * FROM City WHERE CityName in'
SET @where='('+''''+replace(@city,'|',''',''')+''''+')'
SET @Select=@Select+@where
Exec(@Select)

Note: "In my stored procedure I need to replace all pipes with OR operator?" - Since you have multiple values being checked against the same column, better to use IN!

Prakash.P
The secret to creativity is knowing how to hide your sources!

Thanks for reply, i actually meant to say AND operator not OR like in my original post. I think it would change the whole logic that you've put in.

Thanks

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-04-09 : 01:01:07

If you use AND, how can you expect any result? There will be no rows returned if you use AND(except if you specify just one city name)

You have a CITY column and it will have just one value each time!

Prakash.P
The secret to creativity is knowing how to hide your sources!

Access
Starting Member

44 Posts

Posted - 2008-04-09 : 01:28:20

quote:
Originally posted by pravin14u

If you use AND, how can you expect any result? There will be no rows returned if you use AND(except if you specify just one city name)

You have a CITY column and it will have just one value each time!

Prakash.P
The secret to creativity is knowing how to hide your sources!

I've tired to simplify it. I have huge stored procedure with multiple tables where I build WHERE clause dynamically, it is hard to explain. raky got interesting idea, I think it will work.

Thank you

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-04-09 : 02:21:16

I think
a) Why replace pipe with comma - no need if you use raky's method.
b) However you will suffer performance problems with this.
c) Try either full text searching
d) Sort out your API to use arrays of some sort
e) Check out loads of other topics on this subject that turn the separated values into a table then join to it which will be more efficient

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-09 : 02:55:34

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources