You are here:Home » SQL Server General » With Result Sets - TSQL Enhancement in SQL Server Denali

With Result Sets - TSQL Enhancement in SQL Server Denali

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 11 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

GO

USE DBWithResultSet

GO

IF( Object_id('WithResultSetTab') > 0 )
  DROP TABLE WithResultSetTab

GO

CREATE TABLE WithResultSetTab
  (
     LogId   INT IDENTITY (1, 1),
     LogDesc VARCHAR(100),
     LogDate DATETIME
  )

GO

Now populate table WithResultSetTab with some records .

-- Inserting sample records
INSERT WithResultSetTab
SELECT 'Descrption of the Logs occured during testing',
       Getdate()

GO 10


To get the result sets , we are creating stored procedure.

-- Creating procedure to get the data from table
CREATE PROCEDURE Getresultsetfromtab
AS
  BEGIN
      SET nocount ON

      SELECT LogId,
             LogDesc,
             LogDate
      FROM   WithResultSetTab
  END

GO

Let us see the execution of the stored procedure created above,
with and without Result sets feature.

-- Executing stored procedure without Result Sets use 
EXEC Getresultsetfromtab

GO 


-- Executing stored procedure with Result Sets use
EXEC Getresultsetfromtab
WITH RESULT SETS
(
 (
 LogId int,
 LogDetail varchar(10) ,  
 Logdate varchar(20)
 )
)

GO 

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.