none
INSERT Statement Inside a UDF?

    Question

  • I am using SQL Server 2005.  I know you can not put INSERT or UPDATE statements in a UDF.  I am looking for an alternative to getting the end result I need since this call has to go at a specific point in the code or it will never work.  I have a UDF which packs smaller boxes into a bigger box efficiently.  The UDF typically runs and creates as many bigger boxes as possible until all smaller boxes are used and then it returns a TABLE variable back to a stored procedure.  I need to insert a record into a new table at the point where each bigger box is full before it moves on to the next bigger box.  There is only a couple alternatives I can see but don't really know if each is possible.  I am open to an ideas:

    1. Call a stored procedure from the UDF which executes the insert statement

    2. Using EXECUTE to perform the insert statement

    3. Re-write the UDF as a stored procedure.  It must be able to table two inputs and return a table which can be unioned with two other views.

    4. Anything Else

    UPDATE: Scratch idea #1 since it says I can only call other functions or extended stored procedures

    UPDATE: Scratch idea #2 as it did not work

    Friday, August 27, 2010 7:52 PM

Answers

  • SOLUTION: Option #3

    I changed the UDF into a stored procedure and returning a select statement.  I then had to create a TABLE variable back in the calling stored procedure which had the same signature as the former UDF returned table.  I used the statement "INSERT INTO @MyTable EXEC MY_STORED_PROCEDURE" to get the data.  I then used "SELECT * FROM @MyTable in the union statement from before.  All is working great now!

    • Marked as answer by David DeLella Monday, August 30, 2010 1:19 PM
    Monday, August 30, 2010 1:19 PM

All replies

  • Is it a multi-line statement TVF? If yes, can you use table variable to insert record?
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, August 27, 2010 7:58 PM
  • It is a mult-line statement and I am using many TABLE variable to to insert and update the data I am working with.  I am return the table variable I need for work outside the function but in this case it is almost like I need to track the history of the box building process while it is taking place.  The last thing I want to do is break apart this logic into multiple pieces but that may be my only solution because SQL Server is to restricted.  I am preferring Oracle more and more each day.
    Friday, August 27, 2010 8:12 PM
  • It sounds to me that you should use a stored procedure and not a function. Functions in SQL Server should be confined to fairly simple tasks. If find that you need to modify permanent tables from your UDF, you should write a stored procedure instead.

    I looked in a copy of a database of the system I work with. There are over 6000 stored procedures. Less than 100 functions.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Friday, August 27, 2010 10:05 PM
  • SOLUTION: Option #3

    I changed the UDF into a stored procedure and returning a select statement.  I then had to create a TABLE variable back in the calling stored procedure which had the same signature as the former UDF returned table.  I used the statement "INSERT INTO @MyTable EXEC MY_STORED_PROCEDURE" to get the data.  I then used "SELECT * FROM @MyTable in the union statement from before.  All is working great now!

    • Marked as answer by David DeLella Monday, August 30, 2010 1:19 PM
    Monday, August 30, 2010 1:19 PM