Introduction
All the developers are very well-known about the UDF in MS SQL server. The UDF is first introducing at Microsoft SQL Server 2000.
Here in this article we are trying to discuss about some limitation of UDF.
No Side-Effects
By definition, a UDF is supposed to have no side-effects on the database.
In MS SQL Server user defined functions (UDFs) you cannot take any actions (update, delete, insert) that modify data on any object outside the scope of the UDF. A table variable inside the UDF is, of course, allowed.
Let’s take an Example:
IF OBJECT_ID(N'dbo.fn_MYUDF', N'FN')IS NOT NULL
BEGIN
DROP FUNCTION [dbo].[fn_MYUDF];
END
GO
CREATE FUNCTION [dbo].[fn_MYUDF]
(
@p_EMPID INT
)
RETURNS INT
AS
BEGIN
DECLARE @intRETURN INT = 1;
UPDATE dbo.tbl_Employee
SET EMPNAME='Joydeep Das'
WHERE EMPID=@p_EMPID;
RETURN @intRETURN;
END
When we compile this we get an error output.
Msg 443, Level 16, State 15, Procedure fn_MYUDF, Line 10
Invalid use of a side-effecting operator 'UPDATE' within a function.
Can NOT use Non-deterministic Built-in function
Within UDF we cannot use any non-deterministic built in function. Non deterministic functions may return different results each time they are called with a specific set of input values even if the database state that they access remains the same. We cannot influence the determinism of any built-in function. Each built-in function is deterministic or non deterministic based on how the function is implemented by SQL Server.
SQL Server 2005 has allowed some of the non-deterministic functions that were not allowed in 2000. In 2005 we can now use CURRENT_TIMESTAMP, @@MAX_CONNECTIONS, GET_TRANSMISSION_STATUS, @@PACK_RECEIVED, GETDATE, @@PACK_SENT,
GETUTCDATE, @@PACKET_ERRORS, @@CONNECTIONS, @@TIMETICKS, @@CPU_BUSY, @@TOTAL_ERRORS, @@DBTS, @@TOTAL_READ, @@IDLE,
@@TOTAL_WRITE and, @@IO_BUSY
Limitation of Parameters
UDF can have up to 1023 input parameters; Stored Procedure can have up to 21000 input parameters. This is a relatively minor limitation because most routines require a much smaller number of parameters.
Limitation of CURSOR Declaration
IF OBJECT_ID(N'dbo.fn_MYUDF', N'FN')IS NOT NULL
BEGIN
DROP FUNCTION [dbo].[fn_MYUDF];
END
GO
CREATE FUNCTION [dbo].[fn_MYUDF]
(
@p_OPMODE INT
)
RETURNS INT
AS
BEGIN
DECLARE @intRETURN INT;
DECLARE cur_test SCROLL CURSOR
FOR SELECT o.id FROM sysobjects as o;
RETURN @intRETURN;
END
Msg 1049, Level 15, State 1, Procedure fn_MYUDF, Line 0
Mixing old and new syntax to specify cursor options is not allowed.
In UDF we need ANSI SQL style to declare cursor.
In TSQL, user-defined functions cannot modify state of the database or persist information between executions. This is one of the reasons to disallow insert/update/delete on permanent tables or dynamic SQL.
This also means that UDFs will disallow cursors that are global in nature which happens to be the case with those declared using the ANSI SQL syntax. Note that there is no way to specify cursor as local using the ANSI SQL syntax. So we default all cursors declared within UDFs to be local implicitly. This creates a conflict in case of the DECLARE CURSOR statement using ANSI SQL syntax since that doesn't support the local option. Hence the error message about mixing old and new syntax for cursor options. The workaround is to use TSQL syntax for the DECLARE CURSOR statement to specify LOCAL or leave it out in which case we default to LOCAL anyway.
IF OBJECT_ID(N'dbo.fn_MYUDF', N'FN')IS NOT NULL
BEGIN
DROP FUNCTION [dbo].[fn_MYUDF];
END
GO
CREATE FUNCTION [dbo].[fn_MYUDF]
(
@p_OPMODE INT
)
RETURNS INT
AS
BEGIN
DECLARE @intRETURN INT;
DECLARE cur_test CURSOR SCROLL
FOR SELECT o.id FROM sysobjects as o;
RETURN @intRETURN;
END
OR
IF OBJECT_ID(N'dbo.fn_MYUDF', N'FN')IS NOT NULL
BEGIN
DROP FUNCTION [dbo].[fn_MYUDF];
END
GO
CREATE FUNCTION [dbo].[fn_MYUDF]
(
@p_OPMODE INT
)
RETURNS INT
AS
BEGIN
DECLARE @intRETURN INT;
DECLARE cur_test CURSOR LOCAL SCROLL
FOR SELECT o.id FROM sysobjects as o;
RETURN @intRETURN;
END
Return Only One Result Set
UDFs can return only one row set to the user, whereas stored procedures can return multiple row sets.
UDF Can NOT Call Regular Stored Procedure
We can't call regular stored Procedure from functions - only other functions or some extended stored procedures.
-- Procedure
IF OBJECT_ID(N'dbo.proc_MYPROC', N'P')IS NOT NULL
BEGIN
DROP PROCEDURE [dbo].[proc_MYPROC];
END
GO
CREATE PROCEDURE [dbo].[proc_MYPROC]
(
@p_EMPID INT = 0
)
AS
BEGIN
SELECT *
FROM tbl_Employee
WHERE EMPID = @p_EMPID;
END
GO
--Function
IF OBJECT_ID(N'dbo.fn_MYUDF', N'FN')IS NOT NULL
BEGIN
DROP FUNCTION [dbo].[fn_MYUDF];
END
GO
CREATE FUNCTION [dbo].[fn_MYUDF]
(
@p_ID INT
)
RETURNS INT
AS
BEGIN
DECLARE @intRETURN INT;
EXEC [dbo].[proc_MYPROC]
@p_EMPID = @p_ID
RETURN @intRETURN;
END
--Function Execution
SELECT dbo.fn_MYUDF (1)
Msg 557, Level 16, State 2, Line 1
Only functions and some extended stored procedures
can be executed from within a function.
Can NOT Execute Dynamic SQL
UDFs also cannot execute dynamically constructed SQL statements. If we need to construct a statement dynamically based on the parameter values, we must resort to using stored procedures.
Can NOT Support SET Operation
SET options can affect not only the performance of the queries, but their output as well.
SET options cannot be altered within UDFs.
IF OBJECT_ID(N'dbo.fn_MYUDF', N'FN')IS NOT NULL
BEGIN
DROP FUNCTION [dbo].[fn_MYUDF];
END
GO
CREATE FUNCTION [dbo].[fn_MYUDF]
(
@p_ID INT
)
RETURNS INT
AS
BEGIN
DECLARE @intRETURN INT;
SET NOCOUNT ON;
RETURN @intRETURN;
END
Msg 443, Level 16, State 15, Procedure fn_MYUDF, Line 10
Invalid use of a side-effecting operator 'SET OPTION ON' within a function.
Error Checking Limitation
RAISERROR, PRINT statement is not allowed by UDF. Even @@ERROR global variable is not supported by UDF. If you encounter an error, UDF execution simply stops, and the calling routine fails. We are allowed to write a message to the Windows error log with xp_logevent if you have permission to use this extended procedure.
Type of Function
When I am thinking about function, I find several article related to UDF factions in SQL server. There are so many type noted in different article that hardly to remember them.
In my point of view the, UDF function is broadly categorized at 2 types
1. Scalar Function
2. Table Function
I provide you only the simple definition of it, not any complicated boring concepts.
Scalar Function:
It returns only a single value. This value can contains any SQL server data types. The function can accepts multiple parameters with different data types but only return a single value.
This simple example takes 2 decimal values and returns the summation of it. It returns type is also decimal value.
CREATE FUNCTION myFunction
(
-- Add the parameters for the function here
@p_Param1 decimal, ß Input Parameters
@p_Param2 decimal
)
RETURNS decimal ß Returen Type
AS
BEGIN
-- Declare the return variable here
DECLARE @p_ReturnVal decimal
-- Add the T-SQL statements to compute the return value here
SELECT @p_ReturnVal = @p_Param1 + @p_Param2
-- Return the result of the function
RETURN @p_ReturnVal ß Return Value
END
GO
SELECT dbo.myFunction(2,2) Result
GO
Table Function
It returns a table data type and it can be used like a view or table
CREATE FUNCTION myTableFunc
(
@p_CustName Varchar(50) ß Input Parameters
)
RETURNS TABLE ß Returen Type
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT *
FROM ledger_descr WHERE descr LIKE @p_CustName+'%'
)
GO
SELECT *
FROM dbo.myTableFunc('Raja')
GO
