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.



4 comments:

  1. That is very cool, my solution for sql server 2005 and 2008 has been to use table functions instead of stored procs. This is handy also because you can use WHERE and ORDER BY statements with the function.

    Will this new method have any advantage over that?

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Yes Tim,

    This new feature have advantage , I will post for the same and performance review using this method shortly.

    Some time you can not use table valued functions to perform some complex logic or temp table creation, Which you can easily perform with Stored Procedures.

    Also we can have multiple resultset with execution of stored procedure using this feature.

    Thanks for your comment.

    ReplyDelete
  4. CTE's (Common Table Expressions) might be a good alternative for you also

    ReplyDelete