Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

how to create StoredProcedures with variable WHERE condition?


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

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:

>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:

If @vendor is a list, you can push it into some temp table and join on it
using where or join clause.

HTH
Alex

"Roy Harvey" <roy_har@snet.net> wrote in message

news:ccdb63trdo5u8fu6lb5pqrn2497qsnt4c4@4ax.com...

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

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:

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

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc