SQL SERVER – User Defined Functions (UDF) Limitations

UDF have its own advantage and usage but in this article we will see the limitation of UDF. Things UDF can not do and why Stored Procedure are considered as more flexible then UDFs. Stored Procedure are more flexibility then User Defined Functions(UDF).

  • UDF has No Access to Structural and Permanent Tables.
    • UDF can call Extended Stored Procedure, which can have access to structural and permanent tables. (No Access to Stored Procedure)
  • UDF Accepts Lesser Numbers of Input Parameters.
    • UDF can have upto 1023 input parameters, Stored Procedure can have upto 21000 input parameters.
  • UDF Prohibit Usage of Non-Deterministic Built-in Functions
    • Functions GETDATE() etc can not be used UDFs, but can be used in Stored Procedure
  • UDF Returns Only One Result Set or Output Parameter
    • Due to this it can be used in SELECT statement but can not return multiple result set like Stored Procedure
  • UDF can not Call Stored Procedure
    • Only access to Extended Stored Procedure.
  • UDF can not Execute Dynamic SQL or Temporary Tables
    • UDF can not run dynamic SQL which are dynamically build in UDF. Temporary Tables can not be used in UDF as well.
  • UDF can not Return XML
    • FOR XML is not allowed in UDF
  • UDF does not support SET options
    • SET options which can change Server level or transaction level settings are prohibited in UDFs. (SET ROWCOUNT etc)
  • UDF does not Support Error Handling
    • RAISEERROR or @@ERROR are not allowed in UDFs.

As I said earlier this article is written to show Limitations of UDF. I use UDF for many reasons, the main reason I use it I can do repetitive task in SELECT statement as well as modularizing my frequently used code.

Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL and few other articles (Reference Missing)

Previous Post
SQLAuthority News – Author Visit – Meeting with Readers – Top Three Features of SQL SERVER 2005
Next Post
SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility

Related Posts

49 Comments. Leave new

  • it is written here that “UDF has No Access to Structural and Permanent Tables” but I have use this
    and it is working fine

    Reply
  • Vinayaka Magaji
    June 7, 2013 7:16 pm

    Hello Pinal,

    Can you please explain me the with an example for :
    UDF Prohibit Usage of Non-Deterministic Built-in Functions
    Functions GETDATE() etc can not be used UDFs, but can be used in Stored Procedure.
    I’m not getting the Context of this?

    Reply
  • Hi All,

    can we have OUT, IN OUT parameters in function in sql server ?

    Thanks
    Amit Bhardwaj

    Reply
    • You can not. UDF does only return its return value, no output parameters like stored procedures. Guess this is meant by “UDF Returns Only One Result Set or Output Parameter” – the one result/output the one defined in the “returns …” part.

      Reply
  • Hi,

    Why can’t we create temp tables (or Transactions) in UDF ? Any specific reason ?

    Thanks,
    Vipul Patel

    Reply
  • Prasad Pandit
    July 6, 2015 5:45 pm

    Hi,
    We can use GETDATE() in UDF

    Example ……………..
    alter function UFN_MonthName ()

    returns varchar(500)
    as begin
    declare @st varchar(500)

    select @st=DATENAME(month,getdate())

    return @st
    end

    /*
    select dbo.UFN_MonthName() as MonthName
    */

    Reply
  • 1.TRUE or FALSE###;### User Defined Functions cannot be used to modify base table information
    2 Can a UDF be used inside of another UDF?

    Reply
  • Santosh Bastia
    July 18, 2019 12:51 pm

    Stored Procedure can have maximum 2100 input parameters, not 21000 numbers.

    Reply

Leave a Reply Cancel reply

Exit mobile version