WITH RESULT SETS option

Applies to: Microsoft SQL Server 2012.

You can use the WITH RESULT SETS option when you want to execute a stored procedure and would like to change or override the result set returned by the stored procedure. The WITH RESULT SETS option can be used to change number of results sets, the number of columns of the result set, the name of the columns, their data types and their nullability.

On the following example the execution of the stored procedure uspGetBillOfMaterials returns the ProductAssemblyID and the ComponentID fields as "int" and we are overriding the data type of those fields to "nvarchar(20)". We changed also the data types of TotalQuantity and StandardCost fields from "money" data type to "nvarchar(50)". Finally, we changed BOMLevel and RecursionLevel fields from "int" data type to "char(1)" data type.

Limitations.
 

Take in consideration that WITH RESULT SETS is not supported with INSERT ... EXEC.

INSERT ExampleTable (...)
EXEC usp_ExampleStoreProcedure
WITH RESULT SETS ...


References.

Execute (Transact-SQL) on MSDN Library.

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.