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)
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
Nice Change. This will help for better performance.
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
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
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?
great idea – I will have this out this week.
Sir, you gave me great idea!
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
Refer point 2 which exactly does what you wanted
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?
Can we change column datatypes also, as column names?
Thanks a lot really helpful
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.
Its not flexible…have many Limitations
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
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