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 (http://blog.SQLAuthority.com)

About these ads

17 thoughts on “SQL SERVER – 2012 – Executing Stored Procedure with Result Sets

  1. 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

  2. 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
    sqlvillage.wordpress.com

  3. 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?

  4. Pingback: SQL SERVER – Denali – Executing Stored Procedure with Result Sets Journey to SQLAuthority

  5. 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

  6. Pingback: SQL SERVER – Performance Improvement with of Executing Stored Procedure with Result Sets in Denali Journey to SQLAuthority

  7. 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?

  8. 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.

  9. Pingback: SQL SERVER – Weekly Series – Memory Lane – #027 | SQL Server Journey with SQL Authority

  10. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s