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.

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 */
GETDATE() AS MyDate, 1 AS IntValue
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue

Traditional Method:

/* Create TempTable */
/* Run SP and Insert Value in TempTable */
INSERT INTO #tempTable (MyDate, IntValue)
/* SELECT from TempTable */
FROM #tempTable
/* Clean up */
DROP TABLE #tempTable

Alternate Method: Table Valued Function

/* Create table valued function*/
IntValue INT
SELECT GETDATE()+1 AS MyDate, 2 AS IntValue
/* Select data from Table Valued Function */
FROM dbo.TestFn()

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)

About these ads

40 thoughts on “SQL SERVER – Insert Values of Stored Procedure in Table – Use Table Valued Function

  1. 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

  2. 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
    @TableName as x
    dbo.MasterTable as y
    ON x.key = y.key

  3. 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).

  4. 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… )




    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.

  5. 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.

  6. 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

    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


  7. 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?

  8. @Archana,

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

    From Table1

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

    CREATE PROC USP_SelectData
    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

  9. 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))



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


    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.

  10. 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]
    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

    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:

    RETURNS Table
    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
    Set @Select = @Select + ', Taf'+CAST(@i as varchar(2))+' Int';
    Set @i = @i+1;
    Set @tbnameindex = @tbnameindex+1
    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

  11. 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

  12. 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

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


  13. 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…?

  14. 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

  15. Hi sir
    I want how to create procedure dynamically for insert table values

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

    declare @name nvarchar(1000)

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

    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


  16. 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
    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

  17. 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

  18. 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

  19. 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.

  20. 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?

  21. 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?

  22. Pingback: SQL SERVER – Weekly Series – Memory Lane – #048 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s