WITH RESULT SETS IN SQL Server

SQL server 2012 provide with result sets stored procedure execution facility through which we can have better control over the procedure output results, data types and column names.

/*Create Sample Employee Table */

CREATE TABLE Employee(EmpId int, Name Varchar(50),Salary Money)

/* Insert Sample Data in  Table Employee */

INSERT INTO Employee(EmpId,Name,Salary)

select 1,'Ram',100.5

UNION ALL

SELECT 2,'Shayam',115.5

UNION ALL

SELECT 3,'Sita',120

/* Check the inserted Data*/

SELECT * FROM Employee

Scenarios:

Example 1:

/*Create Stored procedure with complete result set*/

Create Procedure ProcEmployee

as

SELECT * FROM Employee

GO

execute ProcEmployee

with result sets

(

(EmployeeId Int, EmployeeName Varchar(1), Salary Int)

)

/*Output

After execution of above stored procedure with, with Result sets clause, it shows the out put column name and data types as per definition in with result sets clause

 */

Example 2:

Create Procedure ProcEmployee_withNoResult

as

SELECT * FROM Employee

where 1=2

GO

execute ProcEmployee_withNoResult

with result sets

(

(EmployeeId Int, EmployeeName Varchar(1), Salary Int)

)

/* OUTPUT

If we execute stored procedure with no result set and execute it with, with result sets clause it executes successfully with no error

*/

Example 3:

CREATE Procedure ProcEmployee_withPrint

as

PRINT 'PrintStmt'

GO

execute ProcEmployee_withPrint

with result sets

(

 ( EmployeeName Varchar(100) )

)

/* OUTPUT

If we execute SP without any result set have just print statement, it shows below error.

PrintStmt

Msg 11536, Level 16, State 1, Line 44

EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

*/

Example 4:

CREATE PROCEDURE ProcEmployee_withZeroSelect

as

select top 0 0

GO

execute ProcEmployee_withZeroSelect

with result sets

(

 ( EmployeeName Varchar(100) )

)

/* OUTPUT

If we execute stored procedure with no result set and execute it with, with result sets clause it executes successfully with no error

*/

Example 5:

CREATE PROCEDURE ProcEmployee_withError

as

select 1/0

GO

execute ProcEmployee_withError

with result sets

( ( EmployeeName Varchar(100) ))

/* OUTPUT

If we execute stored procedure having error while execution, it shows below error

Msg 8134, Level 16, State 1, Procedure ProcEmployee_withError, Line 87

Divide by zero error encountered.

*/

Example 6:

EXECUTE dbo.ProcEmployee

WITH RESULT SETS UNDEFINED;

GO

/* OUTPUT: if we don’t want to change any output value or column we can use UNDEFINED clause

*/

Example 7:

EXECUTE dbo.ProcEmployee

WITH RESULT SETS NONE;

GO

/* OUTPUT: if we execute SP with NONE clause even though its returning data, it shows below error

Msg 11535, Level 16, State 1, Procedure ProcEmployee, Line 97

EXECUTE statement failed because its WITH RESULT SETS clause specified 0 result set(s), and the statement tried to send more result sets than this.

*/