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://darkslategrey-bat-805937.hostingersite.com)
