Very Computer

I have a stored procedure that returns 3 recordsets. For example:

create proc usp_Example
as
select * from table1_tbl

select * from table2_tbl

select * from table3_tbl

Now obviously the sp I have is much more complex and uses several
input parameters.  My question is whether or not it would be better to
encapsulate the select statements into stored procedures and then have
the main procedure call the other 3 procedures.

E.G.,

create proc usp_Example2
as
begin
        EXEC usp_get_table1
end

begin
        EXEC usp_get_table2
end

begin
        EXEC usp_get_table3
end

The reson I am thinking of this approach is that some of those
get_table sps get reused in several places - right now they exist as
blocks of code (select statements) within several different (larger)
stored procedures.

I'd like to take a modular approach, as long as there is a performance
cost.  Can someone give the pros and cons?  Would the first example
execute faster or the second?

this is on SQL 7.

Thanks,

Jack Gardner

Hi Jack,

I have used both ways, with a slight tendacy to prefer the first method. It
really does depend on what & how you are querying. If a DSS enviroment then
you may find the first option will take too long, but if using the second
option then you run the risk of filling the transaction log.

There will be a huge difference if the recordsets were only relatively
small. then it probably would not matter which you choose. I have always
favoured using views to encapsulate as much of the business logic as I could
get away with and then use a 'calling' procedure to run the chain of events.

It would help to know a bit more but I thought I would mention how I
approached this problem.

--
Best Regards

Trevor Dwyer - SQL Server MVP

Quote:> I have a stored procedure that returns 3 recordsets. For example:

> create proc usp_Example
> as
> select * from table1_tbl

> select * from table2_tbl

> select * from table3_tbl

> Now obviously the sp I have is much more complex and uses several
> input parameters.  My question is whether or not it would be better to
> encapsulate the select statements into stored procedures and then have
> the main procedure call the other 3 procedures.

> E.G.,

> create proc usp_Example2
> as
> begin
> EXEC usp_get_table1
> end

> begin
> EXEC usp_get_table2
> end

> begin
> EXEC usp_get_table3
> end

> The reson I am thinking of this approach is that some of those
> get_table sps get reused in several places - right now they exist as
> blocks of code (select statements) within several different (larger)
> stored procedures.

> I'd like to take a modular approach, as long as there is a performance
> cost.  Can someone give the pros and cons?  Would the first example
> execute faster or the second?

> this is on SQL 7.

> Thanks,

> Jack Gardner

On Tue, 13 Jul 1999 03:09:46 +0100, "Trevor Dwyer"

<snip>

Quote:>e will be a huge difference if the recordsets were only relatively
>small. then it probably would not matter which you choose. I have always
>favoured using views to encapsulate as much of the business logic as I could
>get away with and then use a 'calling' procedure to run the chain of events.

>It would help to know a bit more but I thought I would mention how I
>approached this problem.

Given that the recordsets that are being returned are relatively small
(10 rows, 300 rows, 100 rows for the 3 selects respectively) and that
this sp is being used as part of an online transaction system
(virtually every user to this website will call this procedure several
times as it is part of a 'display component' that displays records
from database), I would assume the speed difference between the two
approaches would not be great enough to set up the possible
transaction log problems.

??  

Thanks,

Jack