WHERE Clause Parameter

WHERE Clause Parameter

Post by Steve Sturgi » Sat, 09 Feb 2002 03:27:43



Is there a way to pass a WHERE clause to a stored procedure and use it in a
SELECT statement without using EXECUTE ('select statement' + where clause)?

Here is a sample of a stored procedure and the call to the stored procedure:

CREATE PROCEDURE spReturnData

AS




SELECT * FROM ##Temp

------ Call to the procedure -------------------------------
EXECUTE spReturnData 'WHERE ID = 2'

==========================================================
Is there a way to run the SELECT statement from the spReturnData procedure
without it having to be a wrap procedure? The following SELECT statement
does not work:


Thank you for your help,
Steve Sturgis

 
 
 

WHERE Clause Parameter

Post by Joe Celk » Sat, 09 Feb 2002 03:57:18


1) Read the note at the bottom of this post.  

2)  Is there a way to pass a WHERE clause to a stored procedure and use
it in a SELECT statement without using EXECUTE ('select statement' +
where clause)? <<

NO.  Read the stuff about what dynamic SQL is and how it works.  Also,
this is a horrible idea even if you got it to work without dynamic SQL.
Why are you letting all kinds of garbage come thru into your
application?  Was slow performance and bad ad hoc queries part of the
spec?  You might want to get control over your code by using tricks like
this:

 SELECT <list of appropriate columns>
   FROM <table expression>


    ...

Three are some other tricks with CASE expressions, too.

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

WHERE Clause Parameter

Post by Russell Field » Sat, 09 Feb 2002 04:03:59


Steve,

Not as you describe it, but if we assume that there are a limited set of
possible legal WHERE clauses you could try an IF ELSE structure to choose
which one to execute.   However, any query performance optimization would
pretty much go out of the window with such a wide-open procedure.

Russell Fields
SQL Server MVP


> Is there a way to pass a WHERE clause to a stored procedure and use it in
a
> SELECT statement without using EXECUTE ('select statement' + where
clause)?

> Here is a sample of a stored procedure and the call to the stored
procedure:

> CREATE PROCEDURE spReturnData

> AS




> SELECT * FROM ##Temp

> ------ Call to the procedure -------------------------------
> EXECUTE spReturnData 'WHERE ID = 2'

> ==========================================================
> Is there a way to run the SELECT statement from the spReturnData procedure
> without it having to be a wrap procedure? The following SELECT statement
> does not work:


> Thank you for your help,
> Steve Sturgis

 
 
 

WHERE Clause Parameter

Post by Steve Sturgi » Sat, 09 Feb 2002 04:18:13


Ouch! I didn't know I was letting all kinds of garbage come thru into my
application.

My application provides a list of fields from which users can choose to
create their own ad hoc queries. Only fields that are in the SELECT
statement are included. After they have chosen the fields and provided the
values for the fields, a WHERE clause is constructed in code using the
correct field types with error checking. That is what is passed to the
stored procedure. Yes, sometimes people have challenges with their boolean
logic, but for the most part they can't pass an incorrect value because of
the error trapping in the WHERE clause generator. I would like to use your

Thanks for your input.
Steve Sturgis

P.S. I still carry with me the puzzle from your May 1998 DBMS column.

 
 
 

WHERE Clause Parameter

Post by Michael MacGrego » Sat, 09 Feb 2002 04:22:19


>  SELECT <list of appropriate columns>
>    FROM <table expression>



I did some experimentation with that trick as opposed to say WHERE <column

COALESCE but would do an index scan with it. Sure it works but I couldn't
get it to use an optimal execution plan without providing index hints and I
am reluctant to use those. Any advice?

Michael MacGregor
Database Architect
SalesDriver

 
 
 

WHERE Clause Parameter

Post by Steve Sturgi » Sat, 09 Feb 2002 04:25:21


Russell,
Thanks for your input.
A typical WHERE clause would be:

'WHERE StartDateActual <= "1/23/2002" AND ISNULL(SoldDate,"1/24/2002") >
"1/23/2002" AND (DivisionAbbr <> "arizona" AND Status = "available" AND
StartDateActual is not null  AND ActCloseDate is null )'

The next WHERE clause might not contain any of those fields.

On one report, there are 67 fields from which the users may choose. The
combinations of any of those 67 fields would be exponential.

I appreciate your feedback.
Thanks,
Steve Sturgis

 
 
 

1. Store procedure--TOP Clause parameter

I'm creating the following procedure with the following error:  "incorrect



 AS


  count(requests.srid)as srids ,requests.vendorname
from requests,history ,opendate, closedate
where requests.srid = history.srid and
opendate.srid = closedate.srid and
closedate.srid = requests.srid and
opendate.srid = requests.srid and


history.StatusID = 100
group by requests.vendorname
order by vendorname

2. error 91 Object Variable o on with block variable is not set

3. Evaluation Order of Optional Where Clause Parameters

4. Uniface programmers?

5. IN Clause Parameter

6. *****US-CHI. UNISYS A-SERIES Programming (with COBOL with DMS II)

7. Access Where Clause parameter

8. dbload problem

9. 'in' clause parameters OR view final SQL

10. Parameters in SELECT Clause (SELECT ? AS X)

11. How to pass parameter for an order by clause in stored procedure

12. ODBC Parametrized queries problem : parameter in where clause that can be NULL

13. Creating a complex WHERE clause in a sp with parameters that have multiple values