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 (http://blog.SQLAuthority.com) , BOL and few other articles (Reference Missing)

About these ads

48 thoughts on “SQL SERVER – User Defined Functions (UDF) Limitations

  1. UDF can have upto 1023 input parameters, Stored Procedure can have upto 21000 input parameters.

    it is misprint……

    UDF can have upto 2100 parameters, Stored Procedure can have upto 2100 parameters.

  2. UDFs can certainly access permanent tables, and can also use temp tables (in the form of table variables).

    UDFs should not be compared with stored procedures. They’re completely different things. A TVF is essentially a parameterized view, and a scalar UDF is its own thing entirely. They can each be used in many ways that stored procedures cannot and a comparison really does not make sense.

  3. Thank you Adam,
    I agree UDF and SP are not there to compare, they are apples and oranges. I just tried to compare the apples and oranges and tried to bring the fun on table.
    As I said in beginning in the article. I have wrote this article to show the limitations of UDF, I use UDF for many reasons as well.
    Again, what you said is absolutely correct and I would like to thank you for the adding the supplemental details to this article and making it more complete.
    Regards,
    Pinal Dave (http://www.SQLAuthority.com)

  4. You have given the details about the limitation of parameters for UDF and Stored Procedure in this page..

    But in comments there are some details against your information.. Both are contradictable ..

    So now I got confusion which one is correct…

    So please …tell me the answer properly

    By

    Wilson Gunanithi . J

  5. Hi,
    I ve tried to transfer the data from MS Excel to MS Sql server 2005. But I ve received the error msg as follows :

    ” Microsoft.oledb.jet4.0 ” has not been registered.

    So I could not transfer the data properly.

    So tell me Is there any other option rather than Microsoft.oledb.jet4.0.?

    Or shall I use ODBC for this operation and how?

    Or what can I do complete this process and how?

    Please respond me with the proper answer(With Query).

    Regards,

    Wilson Gunanithi . J

  6. Dear pinal,

    I have one query. I have to track for all the tables in my database if any changes or modifcation happens….
    I want to store all the details like table name,Node(hostname) and dateofmodification with time
    so Is it possible to track in this manner…

    Plz guide me how can I achieve this…

    Its Urgent..
    Thanx in Advance..
    Regards,

    Rupesh

  7. hi, i have this function in ms sql 2000 that returns a varchar(8000). when used in a query, it only returns 256 chars. the output should be 611 in length. what is wrong in my function?
    please help.
    thanks.

  8. gn,

    You are probably looking at this value in query analyzer, you can go to Tools-> Options ->Results pane and you will be able to set the value to a higher value to test your results.. Or you can pull the data from ADO and see what really pops out.

    Good Luck

    Rico

  9. hi,

    i’ve changed the max. char per column to 8000 in qa. when the function was used in the qa, max is now 8000 chars. however, when queried in an execute sql task (ms sql dts object) or written in a text file using transform data task, data was truncated to 256 chars only. i modified my query by splitting the data. each split contains 256 chars and will be concatenated when writing to the file and it worked! i’ve done only 6 splits for now ‘coz that’s the max data in our dev db.

    thanks,
    gn

  10. Pingback: SQL SERVER - 2005 - Use of Non-deterministic Function in UDF - Find Day Difference Between Any Date and Today Journey to SQL Authority with Pinal Dave

  11. Dear sir,

    I hav searched your article to find a solution for my problem.
    I want to create an user defined function which returns the current month …the problem behind this is we cant use getdate() as you said earlier.

    Can any one give a solution….

  12. Hi,

    I want to run dbcc inputbuffer in udf and only return the event info column result.

    I will be using it in select query, can u help

  13. I am developing an accounting package in vb and msde 2000.

    I want to transfer all the tables format, stored procedures, user defined functions etc. created in the current database to newly created database.

    I am creating new year database using sql dmo method.
    I am creating stored procedures in current year using execute method as follows :

    I created a stored procedure in model database which contains code to create all the necessary tables for my application.

    Now I want dynamically, using vb code,
    copy or create user defined functions and stored procedure from my current database to newly created database.

    how it is possible ????????????

  14. Hello Prasad, you can create a view

    CREATE VIEW view_month AS
    SELECT month(getdate()) as current_month

    Then inside the function simply do

    select @month = current_month from view_month

    and return @month

    This is cheating but it works great

  15. Functions GETDATE() etc can not be used UDFs, but can be used in Stored Procedure

    Then, what about the following funtion?

    create function dbo.func_getdate()
    returns nvarchar(50)
    with execute as caller
    as
    begin
    declare @str nvarchar(50)
    set @str = N’now is: ‘ + convert(nvarchar(30), getdate(), 121)
    return @str
    end
    go

    select dbo.func_getdate()
    go

    I create and use it in 2008, I am not sure whether it can work in 2005, 2000,…

  16. Hi,

    Is there any workaround so that I can create temporary UDFs inside a stored procedure ? similar to a temp stored procedure inside another stored procedure…

  17. sir want to create stored procedure to transfer data from machine to another. first it will check the numbers of columns then data type and constrainst . if comparing is teur then only it will transfer data to another machine.

  18. Hey, is it possible for anyone who could assist me to get a UDF function code in SQL for concantinating multiple row values which may relate to the same id in to one column by seperating comma..for a display

    Eg..

    Sale Id Description
    oo1 Good
    001 Very Good
    001 Bad
    002 Not good
    001 Not Working
    002 Working
    001 Defective

    Display must be in the form

    001 Good, Very Good, Bad, Defective
    002 Not Good, Working

  19. Is there any way to return a table from a UDF. (table based UDF) but create the columns dynamically within the UDF? So sometimes you call the UDF and it returns a table with say 2 columns and at another time based on the arguments would return a table with 3 columns?

    Thanks
    MJ

  20. Hi,
    I’m trying to use a table valued function (which returns a table), but the UDF does not allow me to execute a dynamic T-SQL query. Is it possible to return a table from a stored procedure ( I know a stored procedure does not have a return value, but is there some way possible to get a table output) ? I’m not talking about returning data from a temporary table, but the table as a whole. I have another stored procedure which should call the prevous procedure and use it’s output table in a join query.

    Thanks,
    AG

  21. Hi,

    Same problem with AG, let say i have n tables that has identical structure which table name let say tableA, tableB, tableC … next to n. I wanna create an SP that can combine and join between those table(s), and since i can’t use a dynamic T-SQL query to implement this problem any one know what should i do??

    Thanks for all comments
    EC

  22. @AG

    Dynamic SQL cannot be used in Function Period.

    CLR can have dynamic SQL, but it is not suggested to have dynamic SQL in functions.

    @EC

    Please help us to help you, Please give more information, Information you provided is not clear.

    Using Dynamic SQL Anything can be done.

    ~ IM.

  23. UDF can not Return XML
    FOR XML is not allowed in UDF

    Hi,

    I think the above statement is incorrect – I’ve written plenty of UDFs that return XML and use FOR XML.

    Thanks,
    Richard

  24. Hi Pinal,

    I think functions like getdate() can be used in UDFs of SQL Server 2008. Could you please clarify if any other changes to your above mentioned article with respect to UDFs in SQL Server 2008

    Thanks,
    Manesh Joseph

  25. Hi Pinal Dave,

    Please clarify, what is behind the scene..
    It is working in sql server 2005

    create function tt1() returns datetime
    begin
    Declare @s datetime
    select @s=getdate()
    return @s
    end

    select dbo.tt1()

  26. I want to ask one question,Can we update multiple table in one update statement. As per my understanding We can not. Could you please confirm.

  27. Hi, JoeJay,

    The solution for your problem is :

    CREATE TABLE Description_Sales
    (
    Sales_Id VARCHAR(3),
    [Description] VARCHAR(15)
    )

    INSERT INTO Sales VALUES (1,’Good’);
    INSERT INTO Sales (1,’Very Good’);
    INSERT INTO Sales (1,’Bad’);
    INSERT INTO Sales (2,’Not Good’);
    INSERT INTO Sales (1,’Not Working’);
    INSERT INTO Sales (2,’Working’);
    INSERT INTO Sales (1,’Defective’)

    CREATE FUNCTION dbo.allDescr(@id varchar(3))
    RETURNS VARCHAR(max)
    AS
    BEGIN
    DECLARE @descricoes VARCHAR(max)
    SELECT @descricoes = coalesce(@descricoes+’, ‘,”) + Description FROM Description_Sales WHERE Sales_Id = @Id
    RETURN @descricoes
    END
    GO

    select distinct sales_id, dbo.allDescr(sales_id) as Description from Description_Sales order by sales_id

  28. Hi Pinal
    i have a problem
    i have a aspx page of asp.net and there are two calenders
    and a buttton

    i have three tables
    one is task in which i have folowing columns
    userid,application_name,working_hours,finisheddate
    second is team in wich
    userid,salary(cost to a employee),dateofjoining
    third one is leave
    userid,leavedate

    now i want to display on the gridview of asp.net page
    userid,dateofjoining,applicationname,totalleaves,totalWorkingHOurs,FreeHOurs,TotalWorkingCost

    for total working hours there is a condition
    in which all the sundays,leaves and the second and third saturday of every month will be off will be subtracting from the sum of total number of days selected from two calender dates

    for total working cost
    cost will be divided by the sum of working hours to get per hous cost
    and then per hour cost will b multiplied by the hours particularly to get per application cost.

    for free hours
    total working hours -working hours

    i am having problem while doing it…..please help me out…

    Thanx and Regards

  29. i have two tables.
    i fetch designation from first table and check it for second table to list employees that belong that designation.
    i used cursor for both table
    …but it takes lots time..
    any other method for this…..

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

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

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

  33. Pingback: SQL SERVER – Weekly Series – Memory Lane – #043 | 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