Passign a where clause to a SP

Question

Passign a where clause to a SP

Mark Anderson-446267

Mr or Mrs. 500

Points: 545
More actions
June 1, 2007 at 5:26 am

#176873

Hi All

I'm writing a SP that will search all columns in a table. The search criteria will vary a lot (sometimes an exact case-sensitive match is required, sometime I want to use a like clause and sometimes an in clause), so I want to be able to pass the where clause as a parameter. Fro example:
EXEC FindAll " like '%ABC%' "
EXEC FindAll " in ('ABC', 'DEF', 'GHI') "
EXEC FindAll "= 'ABC' "
Can soemone give me a hint of how I can escape the quotes, commas, etc.?
I'm using SQl Server 2005
Thanks
Mark

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

to use the in function on a string, you'll need to grab one of the many SPLIT functions that are in the script contributions here.

otherwise, you really would want to break up the logic into three different procs, instead of an all in one: the reason? a procedure gets it's plan saved when it is created...you'll suffer performance wise if it uses a query different than the one it was originally saved with:

for example, if it was save with a plan that ASSUMED the query would be = @VAR, it would plan an index seek, but if it changed dynamically withing the procedure to something else, the query paln would be incorrect, and would switch to a table scan...if you were searching for a firstname or address or something on a multi-million row table, you'd see a huge hit on performance.

Create Procedure FindLike(@var varchar(100) ) AS

SELECT * FROM SOMETABLE WHERE SOMECOLUMN LIKE '%' + @var +'%'

Create Procedure FindIn(@var varchar(100) ) AS

SELECT * FROM SOMETABLE WHERE SOMECOLUMN IN dbo.Split(@var,',')

Create Procedure FindExact(@var varchar(100) ) AS

SELECT * FROM SOMETABLE WHERE SOMECOLUMN =@var

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Mark Anderson-446267 Mr or Mrs. 500 Points: 545 More actions · Answer 2

Thnaks a lot Lowell

I'm not too concerned about performance, as the DB's I'm working with are pretty small databases (my SP will be run before they go to production enviroments)

Regards

Mark