using where or join clause.
...
> >Use the IN clause instead of all the ORs.
>>Then you can do it as a single parameter for each one.
>>p1_vendor IN(@Vendor)
> Afraid not. A parameter used in an IN clause represents a SINGLE
> VALUE, not a list. That has been explained in this group, over and
> over, for many years.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 05 Jun 2007 11:41:12 -0700, raibeart <raibe@gmail.com>
> wrote:
>>First, change the structure of your SQL.
>>Use the IN clause instead of all the ORs.
>>Then you can do it as a single parameter for each one.
>>WHERE
>>p1_vendor IN(@Vendor)
>>AND
>>p2_productType IN(@ProductType)
>>AND
>>p3_ABC IN (@ABC)
>>Robert
>>On Jun 5, 1:21 pm, Xavier <Xav@discussions.microsoft.com> wrote:
>>> Hello,
>>> i have problem with the creation of a stored procedure with a variable
>>> where
>>> condition.
>>> I have to return the values depending on the where condition which is
>>> created on a web UI.
>>> The parameter are
>>> p1_vendor
>>> p2_productType
>>> p3_ABC
>>> Between the 3 parameter are a AND condition
>>> example:
>>> WHERE
>>> p1_vendor='HP'
>>> AND
>>> p2_productType='LaserJet'
>>> AND
>>> p3_ABC='A'
>>> or
>>> WHERE
>>> p1_vendor='HP' OR p1_vendor='Lexware' OR p1_vendor='Dell'
>>> AND
>>> p2_productType='LaserJet' OR p2_productType='InkJet'
>>> AND
>>> p3_ABC='C'
>>> How can i this achieve this in a stored procedure?
>>> Thanks
>> I have problem with the creation of a stored procedure with a variable WHERE condition. <<
NO! You have a conceptual problem. You have never had a class in
**SOFTWARE ENGINEERING** course. Look up "coupling" and "cohesion" as
ABSOLUTELY FUNDAMENTAL concepts in programming. No module of code
should handle Squids, automobiles and Brittany Spears.
Even MS realized how bad CamelCase is and that vague **physical**
names stink. I do not need to know "p1_vendor"; I meed to know
"vendor_<attribute>" instead. What the hell is a "P1" anyway??
In the old days -- 1957 -- we put one key word on punch card so we
could re-arrange the deck at our desk. So why are you doing the same
thing in the year 2007?? It makes code ~12% to maintain. CamelCase is
also a bitch to read -- the eye jerks to upper case letters.
Here is a direct translation of your code:m
WHERE (vendor_name = 'HP'
AND product_type = 'LaserJet'
AND abc = 'A')
OR (p1_vendor IN ('HP', 'Lexware', 'Dell')
AND product_type IN ('LaserJet', 'InkJet')
AND abc = 'C')
>> How can i this achieve this in a stored procedure? <<
Wrong question! Build a table of (vendor_id, product_type) and use
WHERE EXISTS
(SELECT *
FROM Products AS P
WHERE P.vendor_name = @my_vendor_name,
AND P.poduct_type = @my_product_type)
-----------------------------------------------Reply-----------------------------------------------
Erland Sommarskog has two articles that might help you get ahead:
http://www.sommarskog.se/dynamic_sql.html http://www.sommarskog.se/dyn-search.html ML
---
http://milambda.blogspot.com/
-----------------------------------------------Reply-----------------------------------------------
Xavier,
See if this helps.
Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"Xavier" wrote:
> Hello,
> i have problem with the creation of a stored procedure with a variable where
> condition.
> I have to return the values depending on the where condition which is
> created on a web UI.
> The parameter are
> p1_vendor
> p2_productType
> p3_ABC
> Between the 3 parameter are a AND condition
> example:
> WHERE
> p1_vendor='HP'
> AND
> p2_productType='LaserJet'
> AND
> p3_ABC='A'
> or
> WHERE
> p1_vendor='HP' OR p1_vendor='Lexware' OR p1_vendor='Dell'
> AND
> p2_productType='LaserJet' OR p2_productType='InkJet'
> AND
> p3_ABC='C'
> How can i this achieve this in a stored procedure?
> Thanks
The ordering of logical operators is () > NOT > AND > OR. So, (if I
understand your question correctly) you can use brackets to enforce your
order:
WHERE
(p1_vendor='HP'
AND
p2_productType='LaserJet'
AND
p3_ABC='A')
OR
( (p1_vendor='HP' OR p1_vendor='Lexware' OR p1_vendor='Dell')
AND
(p2_productType='LaserJet' OR p2_productType='InkJet')
AND
(p3_ABC='C') )
HTH,
Paul Ibison
-----------------------------------------------Reply-----------------------------------------------
On 5 Jun, 19:21, Xavier <Xav
@discussions.microsoft.com> wrote:
> Hello,
> i have problem with the creation of a stored procedure with a variable where
> condition.
> I have to return the values depending on the where condition which is
> created on a web UI.
> The parameter are
> p1_vendor
> p2_productType
> p3_ABC
> Between the 3 parameter are a AND condition
> example:
> WHERE
> p1_vendor='HP'
> AND
> p2_productType='LaserJet'
> AND
> p3_ABC='A'
> or
> WHERE
> p1_vendor='HP' OR p1_vendor='Lexware' OR p1_vendor='Dell'
> AND
> p2_productType='LaserJet' OR p2_productType='InkJet'
> AND
> p3_ABC='C'
> How can i this achieve this in a stored procedure?
> Thanks
I don't understood your problem completly but so asking few question.
1 - Does your application ask your to enter ALL search criteria? What
I mean is, user must select ALL 3 search criteria or he is allowed to
select any or non of the search criteria?
2 - Have you tried IN clause instead of OR?
-----------------------------------------------Reply-----------------------------------------------
Xavier,
You can find the answer in the following articles.
Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"Xavier" wrote:
> Hello,
> i have problem with the creation of a stored procedure with a variable where
> condition.
> I have to return the values depending on the where condition which is
> created on a web UI.
> The parameter are
> p1_vendor
> p2_productType
> p3_ABC
> Between the 3 parameter are a AND condition
> example:
> WHERE
> p1_vendor='HP'
> AND
> p2_productType='LaserJet'
> AND
> p3_ABC='A'
> or
> WHERE
> p1_vendor='HP' OR p1_vendor='Lexware' OR p1_vendor='Dell'
> AND
> p2_productType='LaserJet' OR p2_productType='InkJet'
> AND
> p3_ABC='C'
> How can i this achieve this in a stored procedure?
> Thanks
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc
