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:
CREATE PROCEDURE mySP (@ShiftID INT)
SELECT [ShiftID] ,[Name] ,[StartTime] ,[EndTime] ,[ModifiedDate] FROM [HumanResources].[Shift] WHERE [ShiftID] = @ShiftID
-- Executing Stored Procedure
EXEC mySP @ShiftID = 2
WITH RESULT SETS
,[UpdateDate] DATETIME -- Notice Name Change
Reference: Pinal Dave (http://blog.SQLAuthority.com)