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 with Table Valued Function.
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 result set that option 2, where I have converted stored procedure 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. The 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 (https://blog.sqlauthority.com)