Friday, May 20, 2016

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.

No comments:

Post a Comment