SQL SERVER – 2012 – Executing Stored Procedure with Result Sets

Here is a normal conversation I heard when I saw that the function (UDF) was used instead of the procedure (SP).

Q: Why are you using User Defined Function instead of Stored Procedure?

A: I cannot SELECT from SP, but I can from UDF.

SQL Server’s next version ‘Denali’ is coming up with a very interesting feature called WITH RESULT SET.

Using this feature, you can run the stored procedure and rename the columns used in it. The usual procedure of creating TempTable, executing the stored procedure and inserting the data into the TempTable may be time-consuming, that is why Denali introduced the WITH RESULT SET feature. This feature enables you to select the data without using the TempTable. You can rename the columns during the run time as well.

Here is the quick script:

USE AdventureWorks2008R2
GO
CREATE PROCEDURE mySP (@ShiftID INT)
AS
SELECT
[ShiftID] ,[Name] ,[StartTime] ,[EndTime] ,[ModifiedDate] FROM [HumanResources].[Shift] WHERE [ShiftID] = @ShiftID
GO
-- Executing Stored Procedure
EXEC mySP @ShiftID = 2
WITH RESULT SETS
(
(
[ShiftID] TINYINT
,[Name] NVARCHAR(50)
,
[StartTime] DATETIME
,[EndTime] DATETIME
,[UpdateDate] DATETIME -- Notice Name Change
)
);

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Stored Procedure
Previous Post
SQL SERVER – Introduction to SQL Azure – Creating Database and Connecting Database
Next Post
SQL SERVER – 2012 – Executing Stored Procedure with Result Sets – New

Related Posts

15 Comments. Leave new

  • Hi,

    That is really good as many times we need to implement Stored Procedure rather than function to improve the performance.

    I also want to know that, is it mandatory to have same number columns in result sets? Let say I just need to use two columns out of four columns.

    Currently, When we did:

    INSERT INTO Table
    EXEC SP

    This requires to have same nos of columns.

    Thanks in advance.

    Tejas
    SQLYoga.com

    Reply
  • Nice Change. This will help for better performance.

    Reply
  • Its very good, As we really want some time this kind of requirement. Even if there is facility something good providing by microsoft, we can use that.

    Thanks,
    Virul

    Reply
  • Nice Feature,

    As Tejas Suggested more transformations will be nice…

    There is a suggestion posted for this, hope to see this in future relases… :)

    https://docs.microsoft.com/en-us/collaborate/connect-redirect

    Thanks,
    Vishal

    Reply
  • This is a nice feature and i am sure would be used a lot. How is the performance of this as compared with using temp tables?

    Reply
  • Chintan Gandhi
    April 29, 2011 1:46 pm

    What i’d really like to be able to do is get the result of the SP into a table without having to know before hand what are the number and type of columns going to be returned…

    Something like
    SELECT * INTO#tmp_my_result FROM EXEC dbo.My_Sp

    Reply
  • What happens if I want to use the result set in numerous different ways? For example when using a table variable, I get the results, then perform lots of different selects upon it.
    Do we presume here that the PROC will execute again every time the result is referenced?

    Reply
  • Can we change column datatypes also, as column names?

    Reply
  • Thanks a lot really helpful

    Reply
  • Hi Pinal,

    Is this is supported in the sql server 2008 or this feature is avialiable in the sql server 2012 or late. Since when i run this query it throws an error.

    Reply
  • Its not flexible…have many Limitations

    Reply
  • Old fashion way – column name alias:
    USE AdventureWorks2008R2
    GO
    CREATE PROCEDURE mySP (@ShiftID INT)
    AS
    SELECT [ShiftID]
    ,[Name]
    ,[StartTime]
    ,[EndTime]
    ,[ModifiedDate] AS [UpdateDate]
    FROM [HumanResources].[Shift]
    WHERE [ShiftID] = @ShiftID
    GO

    Reply
  • Jonathan Sloan
    April 14, 2016 8:25 pm

    Hi Pinal,

    Once you’ve loaded the dataset via the “WITH RESULT SETS” clause, how can you further manipulate that data within the proc itself? Can you load a #temp table or table variable? Please advise.

    Also, thank you for having one of the single best “SQL Server” resources out there!!!

    Sincerely,

    Jonathan Sloan

    Reply

Leave a Reply