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.

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

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)

, , , ,
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

  • Pinal,

    Thanks for such great updates… this is really helpfull

    ya looking forward for next update…

    Pls make me correct if i am wrong as per my understanding we can’t use all kind of SP in TVF.. (which is limited) we can use only select SP only… we can’t use delete / update statement

    Looking forward for your reply

    Reply
  • Thanks again Pinal! Good examples.

    Reply
  • Sanjiv Kumar S
    October 3, 2009 12:59 am

    Very interesting, I always like to read what you suggest. Thanks.

    Reply
  • Hi…
    I have ans SQL Query that calls the same table name a number of times
    When the table name changes I need to change all references to the table in the query

    I am trying to only have to change the table name once

    simplistically …

    —Begin SQL Query

    ‘Set’ @TableName = ‘Table1’

    SELECT key,column1 FROM @TableName

    SELECT key, column2 FROM @TableName

    SELECT x.column1,y.column3
    FROM
    @TableName as x
    INNER JOIN
    dbo.MasterTable as y
    ON x.key = y.key

    Reply
  • Chuck Lathrope
    October 9, 2009 9:56 am

    Warde, you will need to use dynamic sql as this is the only way to dynamically change the table name. Be sure to use sp_executesql to execute the generated sql script. Books online has great examples of using this function and why you should (cached execution plans).
    -Chuck

    Reply
  • May I add that you are using, in your example, a multi statement table value UDF which can be slower than a stored procedure.

    You could instead use an inline table-value UDF which is simply a shortcut for reusable and more readable code that has no performance issues at all.

    You can also speed things up even more by enabling the schema binding.

    CREATE FUNCTION myFunction
    ( your parameters here… )

    RETURNS TABLE WITH SCHEMABINDING

    AS

    RETURN

    SELECT …
    FROM ….

    I strongly recommend using this kind of table-value UDFs as they are faster than the alternative you have shown and can also be a much faster alternative to views and nested queries in a huge number of cases.

    They can me A LOT faster than views because data can be filtered through parameters (which you can’t do with views) and together with CROSS/OUTER APPLY, they can also speed things up a lot when used instead of nested sub queries, quite often.

    Reply
  • I want get data from stored procedure and want to update my table. Please any body suggest me an syntax for doing this.

    This is what i want to do,

    Update Mytable
    Set Column1 = exec MuProcedure ‘123’

    some thing like above stuf.

    Please help me.

    Reply
  • @Sharan: you can’t select from the resultset of a stored proc directly.

    You’d need to look at OpenQuery for this, although what you are trying to do sounds both inefficient and generally not optimal.

    Reply
  • Hi Sharan,

    What you need to do is.

    You have to add one OUTPUT parameter to your Stored Procedure as:

    CREATE PROC Myprocedure
    @Value INT OUT
    AS

    SELECT @Value = 1.

    Now you need to execute this SP from any query/SP as:

    DECLARE @OutValue INT

    EXEC Myprocedure @Value = @OutValue OUT

    UPDATE Table
    SET Column = @OutValue

    This way you can update the table whatever is return from that SP

    Tejas

    Reply
  • Hey,

    This is a nice and useful article but the thing is we can retrieve the result of the sp as the DATASET and further retrieve the data for own use .Then why there is a need to create the table valued function than Sp?

    Reply
  • Imran Mohammed
    February 5, 2010 12:58 pm

    @Archana,

    Usually when you want to select data from a table, you would write something like this,

    Select
    Cola
    ,Colb
    ,Colc
    From Table1

    but lets say, there is a procedure which does the same thing,

    CREATE PROC USP_SelectData
    AS
    Select
    Cola
    ,Colb
    ,Colc
    From Table1

    Now when you want to retrieve data using stored procedure, then you need to execute procedure.

    Execute USP_SelectData

    But you cannot do a select * from USP_SelectData, this script will not run and will fail, similarly you cannot use stored procedure in your innerquiries.

    So the suggested method is to execute procedure, put the output of procedure into a table and then use that table in your scripts.

    ~ IM

    Reply
  • Sir, You are the best

    Reply
  • Hi,

    I want to update my field columns dynamically. For this i have written a code:
    CREATE PROCEDURE [dbo].[UpdateSheet1]

    (@Sheet1 varchar(50),

    @ColumnName varchar(50)=[Feature Count],

    @NewValue varchar(50))

    AS

    BEGIN

    exec (‘Update ‘+ @Sheet1 + ‘ SET ‘+ @ColumnName +’=”’+ @NewValue+””)

    END

    Here Feature count is my column name. I want to update N number of column field. like Feature count, test cases count,…..)
    What is the exact update procedure for this and my table name is Sheet1.

    Reply
  • thanks,
    it really help me

    Reply
  • Sumit Kumar Gaud
    November 2, 2010 6:59 pm

    I want to know when my procedure is altered last time. If is it possible please answer me

    thank in advance.

    Reply
  • hi
    i want to select my stored procedure result
    my stored procedure make a Sql Commad that return variable columns.
    for example some times my stored procedure result has 5 columns some times more.

    Create procedure [Fnc].[PrcArtyklAllTafsili]
    As
    begin
    Declare @MaxTafLevel smallint, @Select varchar(max), @sql nvarchar(max), @i smallint, @tbnameindex smallint
    Select @MaxTafLevel = MAX(Radif) from Fnc.TBArtTafsili

    Set @Select = ‘Select A.PeriodID, A.CompanyID, A.SanadID, A.ID ArtID, A.KolID, A.MoinID’
    Set @sql = ‘From Fnc.TBArtykl A’
    set @tbnameindex = 98

    Set @i = 1
    While @i = 1
    Exec sp_executesql @sql
    end

    i want to make a function for inserting the result to temp table and select that temp table as result table But in inline table value function i cannot use declare and come other commands
    here is my Functio code:

    CREATE FUNCTION FnTest
    RETURNS Table
    AS
    return
    (
    Declare @MaxTafLevel smallint, @Select nvarchar(max), @i smallint, @tbnameindex smallint
    Select @MaxTafLevel = MAX(Radif) from Fnc.TBArtTafsili

    Set @Select = ‘If Object_ID(”TBtemp”) IS Not Null Drop Table TBTemp Create Table TBTemp(PeriodID smallint, CompanyID Smallint, SanadID Smallint, ArtID smallint, KolID smallint, MoinID smallint’
    set @tbnameindex = 98

    Set @i = 1
    While @i <= @MaxTafLevel
    begin
    Set @Select = @Select + ', Taf'+CAST(@i as varchar(2))+' Int';
    Set @i = @i+1;
    Set @tbnameindex = @tbnameindex+1
    End
    Set @Select = @Select+', Bed numeric(18,0), Bes Numeric(18,0), Note NVarChar(1000))';
    Exec sp_executesql @select
    insert into TBtemp
    Exec Fnc.PrcArtyklAllTafsili

    select *
    from TBtemp
    )

    please help

    Reply
  • is it possible to insert 2 columns value into a temp table instead of inserting whole columns returns from sp

    Reply
    • You can use openrowset function to get required columns from the output of a stored procedure. Refer method 2 of this post for more informations

      Reply
  • Avani Negandhi
    May 26, 2011 2:40 pm

    Hello Pinal,

    I am software engineer, and want the guideline to optimize my queries. i am developing an ERP system, and my database contains around 433 tables ,2681 sps, 128 table-valued and 81 scalar functions. i am facing a big problem of more execution time my some queries are taking.

    Please let me know, what is the procedure to get the regular reply from you, if it will be paid, send me the details

    Reply
  • sir i create one table ,in that table i insert values using Store Procedure,i Create that after that i execute that store Procedure in that time it will show this type of message,”There is already an object named ‘GetMariFamilyRefDat’ in the database”
    Please Correct My Error My store Procedure is

    CREATE PROCEDURE GetMariFamilyRefDat

    AS
    BEGIN
    Insert into MariFamily values(‘Chell’,452,’BSc’)
    end

    GO

    Reply
    • You need to just execute the procedure by

      EXEC GetMariFamilyRefDat

      Reply
    • First reun sp_help GetMariFamilyRefDat
      if you get the results this means there is object named GetMariFamilyRefDat in ur database already. first drop that object and re-execute your code.

      Reply
  • Sir,
    I want to execute my stored procedure and I couldn’t able to find how to run it by passing the values…
    Can you please suggest some other ways to execute…?

    Reply

Leave a Reply Cancel reply

Exit mobile version