In previous version we need to used temporary table to get the result set from stored procedure execution result set for column name change or data type change. SQL Server Denali arrived with one more tsql enhancement, what's it ?
It is "With Result Sets
Le't look on small example to explain this feature.
Creating objects require for Demo.
-- Creating database and table
CREATE DATABASE DBWithResultSet
IF( Object_id('WithResultSetTab') > 0 )
DROP TABLE WithResultSetTab
CREATE TABLE WithResultSetTab
LogId INT IDENTITY (1, 1),
Now populate table WithResultSetTab with some records.
-- Inserting sample records
SELECT 'Descrption of the Logs occured during testing',
To get the result sets , we are creating stored procedure.
-- Creating procedure to get the data from table
CREATE PROCEDURE Getresultsetfromtab
SET nocount ON
Let us see the execution of the stored procedure created above,
with and without Result sets feature.
-- Executing stored procedure without Result Sets use
-- Executing stored procedure with Result Sets use
WITH RESULT SETS
LogDetail varchar(10) ,
Please note the column set should be same in the result sets with execution of the stored procedure.
Share your ideas with more advantage of this feature.