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
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.
These are in SQL SERVER 2005.
and in 2000 and 7.0….. 1024 for both
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.
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
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
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
make a ddl trigers
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.
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
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
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….
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
Hi pinal in your article there is a wrong point that is;
“”UDF can have upto 1023 input parameters, Stored Procedure can have upto 21000 input parameters.””
You said SP’s can accept 21000 parameters But i think this is not 21000 its 2100 for refrence please read the following article by microsoft,
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-2017
Thank you!
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 ????????????
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
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,…
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…
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.
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
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
One more limitation: SQL 2005 does not allow calls to remote table valued functions. You have to use a stored procedure.