redefining names & data types of stored procedure result set + MSSQL 2012

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 293
WITH RESULT SETS option with EXECUTE can be very useful to redefine the display names and data types of result set from a stored procedure.

If you want to create a temp table to hold the result sets of stored procedure, we can make use of WITH RESULT SETS option
CREATE PROCEDURE [dbo].[GetAgents]

@AgentNum VARCHAR(10) = '',
@FirstName VARCHAR(15) = '',
@LastName VARCHAR(30) = '',
@Email VARCHAR(100) = ''
AS
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 + '%' ;
GO
-- Calling SP normally
EXECUTE GetAgents '02341', '', '', ''
GO

-- call SP by changing column names & data type if needed
EXECUTE GetAgents '02341', '', '', ''
WITH RESULT SETS
(
(
AgentCode VARCHAR(10),
AgentName VARCHAR(150),
AgentEmail VARCHAR(100)
)
)



NOTE: Metadata for the result set can be defined by using the WITH RESULT SETS options

Comments or Responses

Login to post response