Redefining structure of more than one result set from a stored procedure

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 197
If stored procedure is returning two or more result sets, then also we can redefine the METADATA of each result set by using WITH RESULT SETS option...

Sample Code
CREATE PROCEDURE [dbo].[GetAgents]

@AgentNum VARCHAR(10) = '',
@FirstName VARCHAR(15) = '',
@LastName VARCHAR(30) = '',
@Email VARCHAR(100) = ''
AS
-- First result set
SELECT A.AgentNum,A.FirstName+ ' ' +A.lastname as Name, A.Email
FROM dbo.AgentTable A
WHERE a.AgentNum LIKE '%' + @AgentNum + '%'
AND a.FirstName LIKE '%' + @FirstName + '%'
AND a.LastName LIKE '%' + @LastName + '%'
AND a.Email LIKE '%' + @Email + '%' ;

-- Second result set
SELECT B.FirstName+ ' ' +B.lastname as Name, B.Phone
FROM dbo.AgentChildTable B
WHERE B.AgentNum LIKE '%' + @AgentNum + '%'

GO
--Normal execution
EXECUTE GetAgents '2312', '', '', ''

EXECUTE GetAgents '2312', '', '', ''
WITH RESULT SETS
(
(
AgentCode VARCHAR(10) NOT NULL,
AgentName VARCHAR(150),
AgentEmail VARCHAR(100)
) ,
(
ChildAgentName VARCHAR(100),
Phone BIGINT NOT NULL
)
)

Comments or Responses

Login to post response