SQL Server EXECUTE Statement with RESULT SET

SQL Server 2012 introduced a RESULT SET clause to the EXECUTE statement.

It can be used to specify alternate data types and column names for result sets returned by an EXECUTED statement or Stored Procedure.

The examples in this article are based on the AdvetureWorks2014 database.

The following example shows its use with an ad-hoc query example.

• The first query uses the RESULT AS clause to define the names datatypes for three returned columns.

• The second query uses a CAST and column aliasing to achieve the same result.

Here is the code for the above queries:

The RESULT SET clause is more useful when working with Stored Procedures that provide no opportunity to change the column names defined within the stored procedure or the data types derived in the underlying Transact SQL code within the procedure.

The following example shows a stored procedure definition and then an EXECUTE statement that changes the column names and data types of the results set returned by the stored procedure.

Here is the code for a sample stored procedure:

Here is the code for the above query:

The following example demonstrates that multiple results sets can be handled where a stored procedure returns more than one result set. Here is the code for a second stored procedure called GetEmployeeNamesAndTitle:

If you would like to learn more why not book on to our SQL Server Database Querying training courses? This link will take you to the course outlines:

http://ptr.co.uk/databases-business-intelligence-courses. We have a full portfolio of SQL Server Training Courses available.