SQL – Execute Stored Procedure with Result Sets

This feature was introduced with SQL Server 2012. It lets you redefine the name and data types of the columns being returned from the stored procedure, and gives you the freedom to avoid any unexpected returning data type errors. 

It allows developers/programmers to handle the output result set if the actual output of the result (stored procedure or function) differs from the expected result set. If the stored procedure is returning more than one result set, it would be useful to define the expected result set as per your implementation needs.

Syntax

Execute a stored procedure or function [ { EXEC | EXECUTE } ]     {       [ @return_status = ]       { module_name [ ;number ] | @module_name_var }         [ [ @parameter = ] { value | @variable [ OUTPUT ]  | [ DEFAULT ] } ]         [ ,...n ]       [ WITH <execute_option> [ ,...n ] ]     } [;] Arguments @return_status: Is an optional integer variable that stores the return status of a module. This variable must be declared in the batch, stored procedure, or function before it is used in an EXECUTE statement. module_name: Is the fully qualified or nonfully qualified name of the stored procedure or scalar-valued user-defined function to call. @parameter: Parameters can be supplied either by using value or by using @parameter_name=value. WITH <execute_option>: Possible execute options. The RESULT SETS options cannot be specified in an INSERT…EXEC statement.

Limitation:

1. The RESULT SETS options cannot be specified in an INSERT…EXEC statement.
2. The number of columns being returned as part of result set cannot be changed.

Using EXECUTE to redefine a single result set

If your stored procedure is returning the single dataset then you can use the single result set to handle all the columns. Number of columns in With Result Set should be the same as the returning output columns as given below:

--- define the stored procedure CREATE PROC proc_GetEmployeeResult AS BEGIN ----- Create table variable DECLARE @EMP_TABLE TABLE ( EMPID INT IDENTITY(1,1), EMPNAME VARCHAR(30), SALARY INT, DEPTNAME VARCHAR(20) ) ---- Fill data into the table variable here INSERT INTO @EMP_TABLE (EMPNAME, DEPTNAME, SALARY) VALUES('RYAN ARJUN','ACCOUNT',35000), ('KIMMY WANG','FINANCE',32000), ('BILL SMITH','HR',25000), ('KYLE BLUE','IT',45000) ----- Pull the output result SELECT EMPID, EMPNAME, DEPTNAME, SALARY FROM @EMP_TABLE; END ---- Execute the stored procedure without using ----- With Result Set feature EXEC dbo.proc_GetEmployeeResult EMPID EMPNAME DEPTNAME SALARY 1 RYAN ARJUN ACCOUNT 35000 2 KIMMY WANG FINANCE 32000 3 BILL SMITH HR 25000 4 KYLE BLUE IT 45000 ---- Execute the stored procedure ----- With Result Set feature EXEC dbo.proc_GetEmployeeResult ----- define the output data type and column names WITH RESULT SETS ( ( ID int,  Empoyee nvarchar(50), Department Varchar(50), Salary decimal(14,2)) ) ID Empoyee Department Salary 1 RYAN ARJUN ACCOUNT 35000.00 2 KIMMY WANG FINANCE 32000.00 3 BILL SMITH HR 25000.00 4 KYLE BLUE IT 45000.00

In the above example, we can see the impact of using With Result Set feature of SQL. It gives us an option to modify the structure of output result set.

Using EXECUTE to redefine multiple result sets

When executing a statement that returns more than one result set, define each expected result set. We can use n… number of With Result Set features but number of columns in With Result Set should be the same as the returning output columns as given below:

---- define the stored procedure CREATE PROC proc_GetEmployeeResult AS BEGIN ----- Create table variable DECLARE @EMP_TABLE TABLE ( EMPID INT IDENTITY(1,1), EMPNAME VARCHAR(30), SALARY INT, DEPTNAME VARCHAR(20) ) ---- Fill data into the table variable here INSERT INTO @EMP_TABLE (EMPNAME, DEPTNAME, SALARY) VALUES('RYAN ARJUN','ACCOUNT',35000), ('KIMMY WANG','FINANCE',32000), ('BILL SMITH','HR',25000), ('ROSY WHITE','HR',25000), ('KYLE BLUE','IT',45000), ('UDAY KAPOOR','ACCOUNT',35000) ----- Result Set 1: Pull the output result SELECT EMPID, EMPNAME, DEPTNAME, SALARY FROM @EMP_TABLE; ----- Result Set 2: Pull the output result SELECT DEPTNAME, TOTAL_SAL=SUM(SALARY) FROM @EMP_TABLE GROUP BY DEPTNAME END ---- Execute the stored procedure without using ----- With Result Set feature EXEC dbo.proc_GetEmployeeResult EMPID EMPNAME DEPTNAME SALARY 1 RYAN ARJUN ACCOUNT 35000 2 KIMMY WANG FINANCE 32000 3 BILL SMITH HR 25000 4 ROSY WHITE HR 25000 5 KYLE BLUE IT 45000 6 UDAY KAPOOR ACCOUNT 35000 DEPTNAME TOTAL_SAL ACCOUNT 70000 FINANCE 32000 HR 50000 IT 45000 ---- Execute the stored procedure ----- With Result Set feature EXEC dbo.proc_GetEmployeeResult ----- define the output data type and column names WITH RESULT SETS ( ( ID int,  Empoyee nvarchar(50), Department Varchar(50), Salary decimal(14,2)), ( Department Varchar(50), TotalSalary decimal(14,2) ) )  ( Department Varchar(50), TotalSalary decimal(14,2) ) ) ID Empoyee Department Salary 1 RYAN ARJUN ACCOUNT 35000 2 KIMMY WANG FINANCE 32000 3 BILL SMITH HR 25000 4 ROSY WHITE HR 25000 5 KYLE BLUE IT 45000 6 UDAY KAPOOR ACCOUNT 35000 Department TotalSalary ACCOUNT 70000 FINANCE 32000 HR 50000 IT 45000

 Conclusion

This is the feature of SQL Server to control the output results and no need to create another replica of the program. We can use this feature also if you program is returning more than one dataset. In the data visualisation, where we can use it to manipulate all the returning datasets as per the requirements.