Thanks, Joe! I broke the statement down similiar to the example you provided
and it worked.
You wouldn't happen to know the best way to combine the results of queries
into a single result set?
EXEC ('SELECT * FROM dbo.tbl_elec_req '+ @whrStmt)
EXEC ('SELECT * FROM dbo.tbl_net_engr '+ @whrStmt)
Regards,
Dave Bosky
Sr. Multimedia Web Designer
Horry Telephone Cooperative, Inc.
office: (843)369-8613
[EMAIL PROTECTED]
-----Original Message-----
From: Joe Eugene [mailto:[EMAIL PROTECTED]
Sent: Monday, March 03, 2003 3:52 PM
To: SQL
Subject: RE: Creating a MSSQL dynamic where clause
I dont think you can create the procedure.. the way you are doing it,
atleast when i tried it with CASE Statments, it didnt work.
You do it this way.. but i am sure.. there other methods as well.
create procedure SomeProc
@somevar int,@somevar2 varchar(20);
as
declare @whrStmt varchar(35);
IF @somevar='something' set @whrStmt='SomeFieldName like '+ '''%Hello%''';
exec ('select * from YourTableName where ' + @whrStmt)
Hope this helps
Joe Eugene
> -----Original Message-----
> From: Bosky, Dave [mailto:[EMAIL PROTECTED]
> Sent: Monday, March 03, 2003 3:18 PM
> To: SQL
> 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
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
