WITH RESULT SETS

The EXECUTE statement has been extended in SQL Server 2012 to include the WITH RESULT SETS option. This allows you to change the column names and data types of the result set returned in the execution of a stored procedure.

We will jump straight into an example to see how this works. The following procedure returns a straightforward result set using the Employee table we created in the previous section:

CREATE PROC spGet_Employees
AS
SELECT EmployeeID, FirstName, LastName
FROM Employee
ORDER BY EmployeeID

If we call this stored procedure in the usual way it will return all columns. The data type of each column will be the same as the column type in the table.

EXEC spGet_Employees

In the previous section, we used the mySequence SEQUENCE object to set the value that was inserted into the EmployeeID column. We want to return the result set so the integer EmployeeID column is a varchar instead. To see how you can easily change the name of the columns, we will output EmployeeID as...