Lets Build a Scenario,
Imagine your need to Standardize your Stored procedure output for dynamic batching. as a matter of fact if the output of your Stored procedure is different from what you expect your query will fail.
So you need to standardize your output from stored procedure. to Do that lets welcome the new SQL Server 2012 T-SQL Feature Result Set.
Result Set will let the format the stored procedure output to map your requirements no matter how the data is formatted internally. Lets take a look using the following code sample
EXECUTE <batch_or_proc> WITH <result_sets_option>;
Sample Code for that is
EXECUTE GetEmployees WITH RESULT SETS(
Some notes to consider when using Result Set
- Sometimes if you want to restrict a stored procedure to return a result set you can use the RESULT SETS NONE clause.
- The WITH RESULT SETS option cannot be specified in an INSERT…EXEC statement.
- The number of columns being returned as part of result set cannot be changed