Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Towards the innovative SQL ideas
Browse by Tags · View All
sql server 125
sql 124
ms sql server 119
ms sql 118
database 109
tsql 81
#SQL Server 78
t-sql 75
#sql 71
sql server general 67

Archive · View All
April 2011 14
July 2011 12
May 2011 12
August 2011 11
June 2011 10
September 2011 8
December 2011 6
November 2011 6
September 2013 5
June 2013 5

TSQL Enhancement in SQL Server Denali - Stored Procedure Execution With Result Sets

May 20 2011 12:00AM by Paresh Prajapati   

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

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.

Tags: sql, tsql, ms sql, ms sql server, t-sql, new features, sql server denali, #SQL Server, mssql, #sql, sql server 2011, database, Stored Procedures,


Paresh Prajapati
6 · 23% · 7454
3
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

1  Comments  

  • very cool...wasn't aware of this feature at all.

    commented on Jun 10 2011 1:10PM
    george14215
    2038 · 0% · 8

Your Comment


Sign Up or Login to post a comment.

"TSQL Enhancement in SQL Server Denali - Stored Procedure Execution With Result Sets" rated 5 out of 5 by 3 readers
TSQL Enhancement in SQL Server Denali - Stored Procedure Execution With Result Sets , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]