Returning Multiple Resultset in a single Stored Procedure is a good practice

Question

I'm having a Stored Procedure in SQL Server it contains Multiple Resultset (i.e., It contains multiple SELECT Statement). Its a good practice of programming ?

For Example:

I have a Person and a Person_Address table.

Create table: Person

CREATE TABLE [dbo].[Person]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [firstName] [varchar](50) NOT NULL,
    [lastName] [varchar](50) NOT NULL,
)

Insert data:

INSERT INTO dbo.Person([firstName], [lastName)
VALUES ('John', 'Michle')

INSERT INTO dbo.Person([firstName], [lastName])
VALUES ('Emma', 'Watson')
GO

Create table Person_Address:

CREATE TABLE [dbo].[Person_Address]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [pid] [int] NOT NULL,   
    [address] [varchar](50) NOT NULL,
    [city] [varchar](50) NOT NULL,
)

Insert data:

INSERT INTO dbo.Person_Address([pid], [address], [city], [ispreferred])
VALUES ('1', 'Eiffel Tower', 'Paris'])

INSERT INTO dbo.Person_Address([pid], [address], [city], [ispreferred])
VALUES ('1', 'Donghai Bridge', 'China')

INSERT INTO dbo.Person_Address([pid], [address], [city], [ispreferred])
VALUES ('2', 'Nile River', 'Egypt')
GO

Stored procedure returning multiple result sets

CREATE PROCEDURE GetPersonInfoMultiRS 
    @PersonID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT p.* FROM Person p WHERE p.id = @PersonID;
    SELECT pa.* FROM Person_Address pa WHERE pa.pid = @PersonID;
END
GO

In the above Stored Procedure GetPersonInfoMultiRS contains two SELECT Statement, In my real project I'm having 18 SELECT Statement. Let me know this is a good practice of programming or not ? I can't able to get a concrete explanation regarding this in google, yahoo and other search engines. Kindly assist me.

score 2 · Answer 1 · answered Jul 27 '16 at 08:19

I'd split into separate procedures.

Generally, there should be a single method doing one single thing. Easier to develop, debug, deploy and maintain.

The SQL Server will not consider the task done until the client consumes the data (this will accumulate as ASYNC_NETWORK_IO wait). You will probably keep the resources for more than necessary. It is more likely to timeout as well.

Further, it's not just 18 resultsets, it's also a lot of code inside the procedure to produce them. This means lot more work for the optimizer if/when the procedure is recompiled, and could take a considerable amount of time.

There are probably some other things I forgot..

Returning Multiple Resultset in a single Stored Procedure is a good practice

1 Answers1