Returning Multiple Resultset in a single Stored Procedure is a good practice
<p>I'm having a Stored Procedure in SQL Server it contains Multiple Resultset (i.e., It contains multiple <strong><code>SELECT</code></strong> Statement). Its a good practice of programming ?</p>

<p>For Example:</p>

<p>I have a <code>Person</code> and a <code>Person_Address</code> table.</p>

<p>Create table: <strong>Person</strong></p>

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

<p>Insert data:</p>

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

INSERT INTO dbo.Person([firstName], [lastName])
VALUES ('Emma', 'Watson')
GO
</code></pre>

<p>Create table <strong>Person_Address</strong>:</p>

<pre><code>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,
)
</code></pre>

<p>Insert data:</p>

<pre><code>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
</code></pre>

<p>Stored procedure returning multiple result sets</p>

<pre><code>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
</code></pre>

<p>In the above Stored Procedure <strong><code>GetPersonInfoMultiRS</code></strong> contains two <strong><code>SELECT</code></strong> Statement, In my real project I'm having 18 <strong><code>SELECT</code></strong> 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.</p>