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

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