none
Functions executed as stored procedures versus stored procedures

    General discussion

  • This discussion relates to using table functions for abstraction instead of stored procedures.

    Assume:

      +  I am not concerned about TRY / CATCH within the body of the function (or stored procedure)
      +  I am not concerned about transactions within the body of the function (or stored procedure)
      +  I know that the function (or stored procedure) will only ever contain read-only operations

    Will execution of the function as a procedure provide similar performance to abstraction as a stored procedure?

    What disadvantages are there to abstraction as a function versus the stored procedure assuming that I will execute the function as a stored procedure?

    Kent Waldrop

    Saturday, July 26, 2014 12:48 PM
    Moderator

All replies

  • I know that the function (or stored procedure) will only ever contain read-only operations

    Thats not true. You can do DML operations inside stored procedures ie insert,update & delete. So its not readonly.

    Functions and stored procedures are used in two different context. Function is mostly used to implement a common logic which needs to be reused at several places. It cant do DML operations on an actual table. It will also use table variables/ select statements inside to do manipulations and return result as a scalar value or a table

    Stored procedure is a precompiled batch of statements which can be encapsulated and called from application to provide abstraction from actual tables in db layer. Stored procedures can also returning values to the calling application.

    And i didnt understand what you meant by I will execute the function as a stored procedure


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, July 26, 2014 1:51 PM
  • This discussion relates to using table functions for abstraction instead

    of stored procedures.

    ...
    What disadvantages are there to abstraction as a function versus the

    stored procedure assuming that I will execute the function as a stored
    procedure?

    There is a contradiction here.

    You can execute a scalar function like a stored procedure, but not a table
    function:

    CREATE FUNCTION scalar (@a int) RETURNS int AS
    BEGIN
       RETURN @a * 99
    END
    go
    CREATE FUNCTION tabular(@a int)
    RETURNS @d TABLE (a int NOT NULL) AS
    BEGIN
       INSERT @d(a) VALUES (@a *99)
       RETURN
    END
    go
    DECLARE @ret int
    EXEC @ret = scalar 2
    SELECT @ret
    go
    DECLARE @ret int
    EXEC @ret = tabular 2
    -- Msg 2809, Level 16, State 1, Line 2
    --The request for procedure 'tabular' failed because 'tabular' is a table
    valued function object.
    SELECT @ret
    go
    DROP FUNCTION scalar
    DROP FUNCTION tabular

    So is your question about scalar functions vs. procedures that returns a
    single value? In this case, I would say there is no significant difference.

    Or is your function about stored procedures that returns a result set vs.
    table-valued function which you would call through a SELECT statement? In
    this case, I would say that the return table in the function is an element
    that can slow things down. Particularly, it will kill parallelism in the
    query plan.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, July 26, 2014 10:02 PM