Combine 2 Stored Procedures - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Combine 2 Stored Procedures

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-02-04 : 11:25:14

Is it possible to combine 2 procedures into 1?

If the first query doesnt return a result, it would then run the 2nd one. Is this good practice?

/* Stored Procedure SalePriceLevels checks for monthly special pricing */
CREATE Procedure SalePriceLevels
(
@Item_No nvarchar(50),
@Start_Dt int,
@End_Dt int
)
AS

SELECT prod_cat, minimum_qty_1, minimum_qty_2, minimum_qty_3, minimum_qty_4, minimum_qty_5, prc_or_disc_1, prc_or_disc_2, prc_or_disc_3, prc_or_disc_4, prc_or_disc_5, price_uom
FROM IMITMIDX_SQL
INNER JOIN OEPRCFIL_SQL ON IMITMIDX_SQL.prod_cat = Substring(OEPRCFIL_SQL.Filler_0001,6,9)
WHERE (IMITMIDX_SQL.item_no = @Item_No)
AND Start_Dt >= @Start_Dt
AND End_Dt <= @End_Dt
AND OEPRCFIL_SQL.Filler_0001 LIKE '02%'

/* If the first query doesn't return a result then run this one */

Create Procedure PriceLevels
(
@Item_No nvarchar(50)
)
AS

SELECT prod_cat, minimum_qty_1, minimum_qty_2, minimum_qty_3, minimum_qty_4, minimum_qty_5, prc_or_disc_1, prc_or_disc_2, prc_or_disc_3, prc_or_disc_4, prc_or_disc_5, price_uom
FROM IMITMIDX_SQL
INNER JOIN OEPRCFIL_SQL ON OEPRCFIL_SQL.Filler_0001 LIKE IMITMIDX_SQL.prod_cat + '%'
WHERE (IMITMIDX_SQL.item_no = @Item_No)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-04 : 11:29:35

it depens on what you want to do if it's good or bad

select * from Table1
if @@rowcount = 0
begin
select * from Table2
end

Go with the flow & have fun! Else fight the flow

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-02-04 : 13:40:11

Thank you, it works the way I want it in EM, but run i run it in QA, it returns 2 result panes, the first with no values and the second with values

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-05 : 08:02:08

of course it returns 2 panels. it runs 2 queries.
if you want to return only one resultset use:

if not exisits(select * from Table1)
select * from Table2
else
select * from Table1

Go with the flow & have fun! Else fight the flow

jrockfl
Posting Yak Master

223 Posts

Posted - 2005-02-05 : 09:20:29

Perfect! That works great for me. That is exactly what I needed.

Thank you, I learned a lot by your posts.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources