asked on

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

lcohan

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

enrique_aeo

ASKER

Give me code T-SQL please

lcohan

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

SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION