> disables the server's ability to precompile the query, so it eliminates
> the optimization you normally receive from a stored procedure.
The SQL-Server Documentation doesnt necessarily say,
"This is what happends or Does NOT happen" executing Exec ("built
query...").
The documentation does say, it caches query "SOME" execution plans but
not strictly "EVERY" Execution Plan.
Personally, i havent seen any performance problems with "Dynamic Procs".
Joe Eugene
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Monday, March 03, 2003 4:30 PM
> To: SQL
> Subject: Re: Creating a MSSQL dynamic where clause
>
>
> The only way to accomplish this is by building your SQL as a string and
> then running the query with the EXEC command. However, this completely
> disables the server's ability to precompile the query, so it eliminates
> the optimization you normally receive from a stored procedure. The CASE
> statement is only valid within a SELECT clause, so your example won't
> work.
> You can lead a horse to water, but if you can get him to swim on
> his back,
> you've got something.
>
>
>
>
>
> "Bosky, Dave" <[EMAIL PROTECTED]>
> 03/03/2003 03:17 PM
> Please respond to sql
>
>
> To: SQL <[EMAIL PROTECTED]>
> cc:
> Subject: Creating a MSSQL dynamic where clause
>
>
> I want to pass in a variable and use it to decide which where clause to
> execute.
> The problem lies within my case statement somewhere. This is one of my
> first
> attempts at creating a stored procedure in MSSQL. Would there be a better
> way
> to do it?
>
> ------------------------------
> CREATE PROCEDURE dbo.Supply
> @Opt int,
> @item_id varchar (26) = NULL,
> @item_descp varchar (35)= NULL,
> @commodity_code char (4) = NULL,
> @Stock varchar (8) = NULL
> AS
>
> SELECT site_id,usage_code,item_id,item_descp,commodity_code,on_hand_qty
> FROM dbo.tbl_net_engr
>
> WHERE item_id like '%' + COALESCE(@item_id,item_id) + '%'
> AND item_descp like '%' +COALESCE(@item_descp,item_descp) + '%'
> AND commodity_code like '%' + COALESCE(@commodity_code,commodity_code) +
> '%'
>
> CASE
> WHEN @Stock='InStock' THEN AND on_hand_qty GT 0
> WHEN @Stock='OutStock' THEN AND on_hand_qty = 0
> WHEN @Stock='AllStock' THEN AND on_hand_qty => 0
> END
> ---------------------------------
> Regards,
>
> Dave Bosky
> Sr. Multimedia Web Designer
> Horry Telephone Cooperative, Inc.
> office: (843)369-8613
> [EMAIL PROTECTED]
>
>
>
> HTC Disclaimer: The information contained in this message may be
> privileged and confidential and protected from disclosure. If the reader
> of this message is not the intended recipient, or an employee or agent
> responsible for delivering this message to the intended
> recipient, you are
> hereby notified that any dissemination, distribution or copying of this
> communication is strictly prohibited. If you have received this
> communication in error, please notify us immediately by replying to the
> message and deleting it from your computer. Thank you.
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
