10774: alternatives to the OR sentence (dinamyc query)

Hi experts:

I have a store procedure that can take 10 parameters. The user can enter 1,2,3 to 10 parameters. Do it OR is very expensive, can show T-SQL code to solve

SELECT
  *
  FROM TSQL2012.Production.Suppliers
  where city = @city OR phone = @phone OR contacttitle = @contacttitle OR country=@country
   ............. parametro10 = @parametro10
enrique_aeoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
Ever thought a UNION may work better? it implies DISTINCT so no duplicate rows will exists.
Or INTERSECT to get the row "identifier" in a CTE for instance than use that limited CTE record set to JOIN with actual table and get the rest of the columns
0
enrique_aeoAuthor Commented:
Give me code T-SQL please
0
lcohanDatabase AnalystCommented:
SELECT * FROM TSQL2012.Production.Suppliers
WHERE city = @city
UNION
SELECT * FROM TSQL2012.Production.Suppliers
WHERE phone = @phone
UNION
SELECT * FROM TSQL2012.Production.Suppliers
WHERE contacttitle = @contacttitle
UNION
SELECT * FROM TSQL2012.Production.Suppliers
WHERE country= @country

And make sure you have an index on each of the above columns

And then you build your dynaminc SQL by not having in it the
...
UNION
SELECT * FROM TSQL2012.Production.Suppliers
WHERE country= @country
...

statement(s) where parameter value passed in IS NULL

and you execute the dynamic string by calling SP_EXECUTESQL
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

lcohanDatabase AnalystCommented:
Or here - I think this is even better because it does not require dynamic SQL as the IS NOT NULL is included on each "branch" of the query. So all you need is pass NULL from app to the parameters you don't want/need in the SP and that particular select will return noting.



SELECT * FROM TSQL2012.Production.Suppliers
WHERE city = @city and @city IS NOT NULL
      UNION
SELECT * FROM TSQL2012.Production.Suppliers
WHERE phone = @phone and @phone IS NOT NULL
      UNION
SELECT * FROM TSQL2012.Production.Suppliers
WHERE contacttitle = @contacttitle  and @contacttitle IS NOT NULL
      UNION
SELECT * FROM TSQL2012.Production.Suppliers
WHERE country= @country and @country IS NOT NULL
0
Scott PletcherSenior DBACommented:
You really must use dynamic SQL to get good performance from these types of generic queries.  You can use sp_executesql, rather than EXEC, to drastically reduce the chance of SQL injection.
0
enrique_aeoAuthor Commented:
ScottPletcher:

can you give me an example dynamic SQL
0
Scott PletcherSenior DBACommented:
Here's the simplest method, although you'll want to add checks to prevent SQL injection using this method:


DECLARE @sql varchar(max)
SET @sql = '
SELECT *
FROM TSQL2012.Production.Suppliers
WHERE 1 = 1'
IF @city > ''
    SET @sql = @sql + '
    AND (@city = city)'
IF @phone > ''
    SET @sql = @sql + '
    AND (@phone = phone)'
IF @contracttitle > ''
    SET @sql = @sql + '
    AND (@contacttitle = contacttitle)'
--IF ...other_parms...
    --SET ... + ...

EXEC (@sql)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.