As a SQL Server developer, you may often come across situations where you need to insert the values returned by a stored procedure into a table. In this article, we will explore two different methods to achieve this – the traditional method using a temporary table and an alternate method using a table-valued function.

The Traditional Method: Using a Temporary Table

The traditional method involves creating a temporary table and then inserting the values from the stored procedure into this table. Let’s take a look at the code:

/* Create TempTable */
CREATE TABLE #tempTable (MyDate SMALLDATETIME, IntValue INT)

/* Run SP and Insert Values in TempTable */
INSERT INTO #tempTable (MyDate, IntValue)
EXEC TestSP

/* Select Data from TempTable */
SELECT *
FROM #tempTable

/* Clean up */
DROP TABLE #tempTable

In this method, we first create a temporary table with the same structure as the resultset of the stored procedure. We then use the INSERT INTO statement along with the EXEC command to insert the values returned by the stored procedure into the temporary table. Finally, we can select the data from the temporary table as needed.

The Alternate Method: Using a Table-Valued Function

An alternate method to insert values from a stored procedure into a table is by using a table-valued function. Let’s take a look at the code:

/* 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

/* Select Data from Table Valued Function */
SELECT *
FROM dbo.TestFn()

In this method, we create a table-valued function that returns a table with the same structure as the resultset of the stored procedure. We then use the function in a SELECT statement to retrieve the data.

Comparing the two methods, it is clear that the second option, using a table-valued function, is more efficient in terms of logic as it saves a large number of operations. However, it is important to note that the performance of a stored procedure is usually better than that of a function. It is recommended to use the table-valued function method carefully and consider the specific requirements of your application.

In a future post, we will discuss the types of stored procedures that can be converted into table-valued functions. Stay tuned!

Let us know your thoughts on this article in the comments below.