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

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

    Like

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

    Like

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

    Like

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

    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.

    Like

  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.

    Like

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

    Like

  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?

    Like

  8. @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

    Like

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

    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.

    Like

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

    Like

  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

    Like

  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

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

    GO

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  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?

    Like

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