> Thanks, Joe! I broke the statement down similiar to the example
> you provided
> and it worked.
No problem, Good.
> 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)
Its possible but not straightforward
1. You can create a #Temptable and store each ResultSet in the #TempTable
Some thing like..
Create Table #myTempTable(field1 int,field2 varchar(20))
INSERT INTO #myTempTable(field1,field2)
select someField1 , someField2 from SomeTable
select * from #myTempTable
2. If you tables dbo.tbl_elec_req & dbo.tbl_net_engr have any relationship,
you can do a join or if same kind of fields.. UNION is possible.
2.You can return multiple ResultSets/Querys, call the Proc with
"CFStroredProc"
but i havent noticed any performance doing this..especially returning large
ResultSets.
Depending you specific situtation, one of the above should work.
Hope this helps.
Joe Eugene
> -----Original Message-----
> From: Bosky, Dave [mailto:[EMAIL PROTECTED]
> Sent: Monday, March 03, 2003 4:47 PM
> To: SQL
> Subject: RE: Creating a MSSQL dynamic where clause
>
>
> 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
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
