I recently got many emails requesting to write a simple article. I also got a request to explain different ways to insert the values from a stored procedure into a table. Let us quickly look at the conventional way of doing the same.
Please note that this only works with the stored procedure with only one resultset. Let us create a stored procedure that returns one resultset.
/*Create Stored Procedure */ CREATE PROCEDURE TestSP AS SELECT GETDATE() AS MyDate, 1 AS IntValue UNION ALL SELECT GETDATE()+1 AS MyDate, 2 AS IntValue GO
/*Create TempTable */ CREATE TABLE #tempTable (MyDate SMALLDATETIME, IntValue INT) GO /* Run SP and Insert Value in TempTable */ INSERT INTO #tempTable (MyDate, IntValue) EXEC TestSP GO /* SELECT from TempTable */ SELECT * FROM #tempTable GO /* Clean up */ DROP TABLE #tempTable GO
Alternate Method: Table Valued Function
/* Create table valued function*/ CREATE FUNCTION dbo.TestFn() RETURNS @retTestFn TABLE ( MyDate SMALLDATETIME, IntValue INT ) AS BEGIN DECLARE @MyDate SMALLDATETIME DECLARE @IntValue INT INSERT INTO @retTestFn SELECT GETDATE() AS MyDate, 1 AS IntValue UNION ALL SELECT GETDATE()+1 AS MyDate, 2 AS IntValue RETURN; END GO /* Select data from Table Valued Function*/ SELECT * FROM dbo.TestFn() GO
It is clear from the resultset that option 2, where I have converted stored procedures logic into the table valued function, is much better in terms of logic as it saves a large number of operations. However, this option should be used carefully. Performance of the stored procedure is “usually” better than that of functions.
We will discuss in another post regarding the type of stored procedure that can be converted into a table valued function. Let me know what you all think about this post.
Reference : Pinal Dave (http://blog.SQLAuthority.com)