The EXECUTE Statement with RESULT SETS Clause

SQL Server 2012 introduces the WITH RESULT SETS clause for the EXECUTE statement. Using this clause, you can change conditionally the form of the result set of a stored procedure.

The following two examples help to explain this clause. Example 8.10 is an introductory example that shows how the output looks when the WITH RESULT SETS clause is omitted.

USE sample;
GO
CREATE PROCEDURE employees_in_dept (@dept CHAR(4))
	AS SELECT emp_no, emp_lname
		FROM employee
		WHERE dept_no IN (SELECT @dept FROM department
			GROUP BY dept_no)Code language: PHP (php)

employees_in_dept is a simple stored procedure that displays the numbers and family names of all employees working for a particular department. (The department number is a parameter of the procedure and must be specified when the procedure is invoked.) The result of this procedure is a table with two columns, named according to the names of the corresponding columns (emp_no and emp_lname). To change these names (and their data types, too), SQL Server 2012 supports the new WITH RESULTS SETS clause. Example 8.11 shows the use of this clause.

USE sample;
EXEC employees_in_dept 'd1'
	WITH RESULT SETS
	( ([EMPLOYEE NUMBER] INT NOT NULL,
		[NAME OF EMPLOYEE] CHAR(20) NOT NULL));Code language: PHP (php)

The output is

As you can see, the WITH RESULT SETS clause in Example 8.11 allows you to change the name and data types of columns displayed in the result set. Therefore, this new functionality gives you the flexibility to execute stored procedures and place the output result sets into a new table.

Changing the Structure of Stored Procedures

The Database Engine also supports the ALTER PROCEDURE statement, which modifies the structure of a stored procedure. The ALTER PROCEDURE statement is usually used to modify Transact-SQL statements inside a procedure. All options of the ALTER PROCEDURE statement correspond to the options with the same name in the CREATE PROCEDURE statement. The main purpose of this statement is to avoid reassignment of existing privileges for the stored procedure.

A stored procedure (or a group of stored procedures with the same name) is removed using the DROP PROCEDURE statement. Only the owner of the stored procedure and the members of the db_owner and sysadmin fixed roles can remove the procedure,