This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C17D11.183CC97C
Content-Type: text/plain;
	charset="iso-8859-1"

dynamically generated Select statements are typically slow, there are better
options. I have a search form that has ~10 fields. Any combination of these
fields is permissible, the only requirement is that one is entered. The
WHERE clause uses: 
(MyField = @Var1 OR MyField IS NULL)

Using this for all your conditions you can pass through NULL for all the
parameters where you don't have a value, which means they will not have any
effect.

Another handy trick is to use a CASE statement in an ORDER BY clause, but
this can only be used for that same data type.

regards
David Cameron
nOw.b2b
dcameron@i...

-----Original Message-----
From: Xu Gary [mailto:Gary.Xu@p...]
Sent: Wednesday, 5 December 2001 5:50 AM
To: sql language
Subject: [sql_language] Re: Dynamically creating WHERE clause within a
stored procedure


Yes you can write a dynamically created WHERE clause within a stored
procedure. But unless your database is small with low usage and will stay
that way for ever, you should write the similiar select in different SP's.
For more information, please search Execution Plan Caching and Reuse in BOL.

Gary Xu


-----Original Message-----
From: kathy.egan@h... [mailto:kathy.egan@h...]
Sent: Tuesday, December 04, 2001 7:08 AM
To: sql language
Subject: [sql_language] Re: Dynamically creating WHERE clause within a
stored procedure


I was trying to stay away from repeating the fields and tables lists.  I'm 
selecting approximately 20 fields with a lot of formatting 
(example:, 
rtrim(convert(char, T.createDate, 1))
+ '   '
+ replicate('0', 2 - datalength(rtrim(datepart(hh, T.createDate))))
+ rtrim(convert(char(2), datepart(hh, T.createDate)))
+ replicate('0', 2 - datalength(rtrim(datepart(mi, T.createDate)))) 
+ rtrim(convert(char(2), datepart(mi, T.createDate)))
as createDate
)
My tables list is 9 tables with both inner and left outer joins.  This is 
why I'm looking for a way to change only the WHERE statement at execution 
time.


> You can. Try this:
> 
> create proc spYourProcNameHere @wheretype int
> as
> Declare @whereStmt varchar(500)
> if @whereType = 1
>     set @whereStmt = "select * from table1 where field1 = 1"
> else
>     set @whereStmt = "select * from table1 where field1 = 2"
> execute(@whereStmt)
> 
> 
> ----- Original Message -----
> From: <kathy.egan@h...>
> To: "sql language" <sql_language@p...>
> Sent: Tuesday, December 04, 2001 2:40 PM
> Subject: [sql_language] Dynamically creating WHERE clause within a
> stored procedure
> 
> 
> I'm creating a stored procedure that will be used in many places
> within an
> application.  The SELECT and FROM clause are the same for every call.
> The
> only statement which changes is the WHERE clause.  In pseudo-code,
> this is
> what I want to do:
> ~~~~
> create procedure sp1
> 
> @whereType int
> as
> declare @whereStmt varchar(20)
> 
> if @whereType = 1
> set @whereStmt = 'field1 = 1'
> else
> set @whereStmt = 'field2 = 2'
> 
> select *
> from table1
> where @whereStmt
> ~~~~
> Is this possible?  And, if so, what is the proper syntax?
> 
> $subst('Email.Unsub')
> 
> 
> 





This electronic message transmission, including any attachments, contains
information from PacifiCare Health Systems Inc. which may be confidential or
privileged. The information is intended to be for the use of the individual
or entity named above. If you are not the intended recipient, be aware that
any disclosure, copying, distribution or use of the contents of this
information is prohibited.  

If you have received this electronic transmission in error, please notify
the sender immediately by a "reply to sender only" message and destroy all
electronic and hard copies of the communication, including attachments.