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.
Now populate table WithResultSetTab with some records .
To get the result sets , we are creating stored procedure.
Let us see the execution of the stored procedure created above,
with and without Result sets feature.
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.
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.
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.
ReplyDeleteWill this new method have any advantage over that?
This comment has been removed by the author.
ReplyDeleteYes Tim,
ReplyDeleteThis 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.
CTE's (Common Table Expressions) might be a good alternative for you also
ReplyDelete