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)
49 Comments. Leave new
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
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
@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.
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
Can we use DML statement in UDF?
Hello Paul,
No, we can’t use DML in UDF. We can not perform any activity in UDF that makes any change effect on data.
Regards,
Pinal Dave
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
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()
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.
Hi Pinal Dave,
Why Can’t we use order by clause in view
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
Can I use union in Table value function.
User-defined functions do not support output parameters.
Hi PinalDave,
does this statement correct?
UDF Returns Only One Result Set or Output Parameter
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
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…..
I read from MSDN there are max 2100 input parameters in Stored procedure.
it is written here that “UDF has No Access to Structural and Permanent Tables” but I have use this
and it is working fine
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?
Hi All,
can we have OUT, IN OUT parameters in function in sql server ?
Thanks
Amit Bhardwaj
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.