SQL SERVER – Insert Values of Stored Procedure in Table – Use Table Valued Function

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.

SQL SERVER - Insert Values of Stored Procedure in Table - Use Table Valued Function j2p_26_5-800x318

Please note that this only works with the stored procedure with only one resultset. Let us create a stored procedure that returns one resultset.

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

Traditional Method:

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

Solarwinds
, , , ,
Previous Post
SQLAuthority News – Article 1100 and Community Service
Next Post
SQL SERVER – Interesting Observation – Index on Index View Used in Similar Query

Related Posts

46 Comments. Leave new

  • I am new to this site sir, i am very happy to see this site.it’s really helpful sir,

    Reply
  • i have to add another another button of adding details using add burtton
    i want stored procedure for inserting values and the values should dispaly in clicking the required button

    Reply
  • Hi sir
    I want how to create procedure dynamically for insert table values

    means
    creaet proc tbl(@tblname varchar(100))as
    begin

    declare @name nvarchar(1000)

    select @name=’select * from’ +char(39)+ @tblname +char(39)
    print @name
    end

    so finally we got out put like this
    select * from ’emp’

    then we coppy and past upper and then execute the query

    similarly i want insert procedure dynamically when i will give a table name
    in procedure at that time

    please help me

    regards
    nareshreddy

    Reply
  • hi,
    My procedure returns three tables as it has three select statement inside.
    now I want to Insert result in different three tables.
    How to do that?

    eg. proc
    create proc test
    as
    begin
    select * from table1 —result to be inserted into R_table1
    select * from table2 —result to be inserted into R_table2
    select * from table3 —result to be inserted into R_table3
    end

    Reply
  • Dewesh Pushkar
    April 18, 2012 10:18 am

    Hi Sir,
    If I am creating a SP and in that I have to insert data in a table and I want input another sp retuned value in the column of that table so how can I insert the value of sp to a column of a table.
    Plase help me out.
    Thanks &regard
    Dewesh Pushkar

    Reply
  • Michael Hinkle
    August 14, 2012 8:30 pm

    I have 3 tables (AccountNum, Details, and Main). I need to upload a purchase into these 3 tables. The main is the main, and AccountNum and Details are tables that many have multiple entries per main entry. I would like to use a Stored Procedure to insert values into these tables. I am using VWD 2010. Any help would be helpfule.

    Mike Hinkle

    Reply
  • I create a store procedure to check the validity of ID numbers.
    store proc gets the Id number as an argument.
    Id_numbers are stored in a table. i want to pass the Id_number column as an argument and update validity on another column in same table . any help would be appreciated.

    Reply
  • Why we use stored procedure?why we cannot directly create table?why we first declared a table and in that we create a stored procedure?

    Reply
  • Why we use stored procedure?
    why we cannot directly create table?
    why we first declared a table and in that we create a stored procedure?

    Reply
  • babulal prajapat
    April 24, 2014 7:19 pm

    hi…..sir this best for me
    i am sql user

    Reply
  • Nilesh Kabra
    July 7, 2015 5:06 pm

    One of the variables that my stored procedure is returning is a timestamp. I call this Stored Procedure from a different Stored Procedure and inserts into a @table. But it gives an error:

    Msg 273, Level 16, State 1, Procedure , Line 193
    Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

    Reply
    • Nilesh – that’s an expected behavior. If datatype of a column is timestamp, you can’t insert values. What exactly you are tying to achieve?

      Reply
      • Nilesh Kabra
        July 14, 2015 2:00 pm

        It is resolved now:

        I had declared as the @tmpTable (in which i am saving the values returned by my Stored Procedure call) to receive value of timestamp which is why I was getting the error:
        DECLARE @SP_OUTPUT TABLE (
        ,
        .,
        .,
        [COLUMN_TIMESTAMP] timestamp NULL
        )

        But I wanted to track the output that the stored procedure was returning. So I made the following change:

        DECLARE @SP_OUTPUT TABLE (
        ,
        .,
        .,
        [COLUMN_TIMESTAMP] BIGINT NULL
        )

        Changing the datatype to BIGINT resolves all the issues with timestamp.

      • Good. I am glad that my tip helped you.

  • You can do it without creating a table type by calling the store procedure with dummy value to create table type and then use that table to insert the value

    SELECT *
    INTO #tmpTable
    FROM OPENQUERY(DATABASESERVERNAME,’ YOURPROCEDURECALL ”-1”’ )

    insert into #tmpTable
    exec(YOURPROCEDURECALL @ACTUALVALUE )

    Reply
  • try this
    create procedure spTS_BatchEntry
    @stage varchar(10),
    @BatchNo int,
    @BatchRefNo int,
    @BatchDate datetime,
    @Duration int,
    @InputQty float,
    @OutputQty float
    as
    begin
    insert into [dbo].[TS_Sys_BatchEntry] (
    [Stage],[BatchNo],[BatchRefNo],[BatchDate],[Duration],[InputQty],[OutputQty]
    )values( @stage,@BatchNo,@BatchRefNo,@BatchDate,@Duration,@InputQty,@OutputQty)
    end

    Reply
  • I want to call above stored procedure in wcf service, can anyone tell me how i can do it

    Reply

Leave a Reply

Menu