How to Report an Error from a SQL Server User-Defined Function

How to report an error from a SQL Server user-defined function

You can use CAST to throw meaningful error:

create function dbo.throwError()
returns nvarchar(max)
as
begin
    return cast('Error happened here.' as int);
end

Then Sql Server will show some help information:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Error happened here.' to data type int.

Error Handling in User Defined Functions

It seems that SQL Server UDF's are a bit limited in this (and many other) way.

You really can't do a whole lot about it - that's (for now) just the way it is. Either you can define your UDF so that you can signal back an error condition by means of its return value (e.g. returning NULL in case of an error), or then you would almost have to resort to writing a stored procedure instead, which can have a lot more error handling and allows RAISERROR and so forth.

So either design your UDF to not require specific signaling of error conditions, or then you have to re-architect your approach to use stored procedures (which can have multiple OUTPUT parameters and thus can also return error code along with your data payload, if you need that), or managed CLR code for your UDF's.

Sorry I don't have a better idea - for now, I'm afraid, those are your options - take your pick.

Marc

Sql server user defined function BEGIN sentence drops out error

For Inline Table-Valued Function, this is the MS SQL syntax.

CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName> 
(   
    -- Add the parameters for the function here
    <@param1, sysname, @p1> <Data_Type_For_Param1, , int>, 
    <@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE 
AS
RETURN 
(
    -- Add the SELECT statement with parameter references here
    SELECT 0
)
GO

For Multi-statement Table-Valued Function, this is the MS SQL syntax.

CREATE FUNCTION <Table_Function_Name, sysname, FunctionName> 
(
    -- Add the parameters for the function here
    <@param1, sysname, @p1> <data_type_for_param1, , int>, 
    <@param2, sysname, @p2> <data_type_for_param2, , char>
)
RETURNS 
<@Table_Variable_Name, sysname, @Table_Var> TABLE 
(
    -- Add the column definitions for the TABLE variable here
    <Column_1, sysname, c1> <Data_Type_For_Column1, , int>, 
    <Column_2, sysname, c2> <Data_Type_For_Column2, , int>
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set

    RETURN 
END
GO

In your case, you are using Inline Table-Valued Function, so that Begin is not allowed.

Try-Catch in User Defined Function?

From MSDN:

A column or local variable of
uniqueidentifier data type can be
initialized to a value in the
following ways:

By using the NEWID function.

By converting from a string constant
in the form
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx,
in which each x is a hexadecimal digit
in the range 0-9 or a-f.

For example,
6F9619FF-8B86-D011-B42D-00C04FC964FF
is a valid uniqueidentifier value.

You can use pattern matching to verify the string. Note that this won't work for specific encoding that reduces the size of the GUID:

declare @Project nvarchar(50) 

declare @ProjectID uniqueidentifier 
declare @HexPattern nvarchar(268) 

set @HexPattern =  
    '[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]' +  
    '[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]' +  
    '[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]' +  
    '[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]' 

/* Take into account GUID can have curly-brackets or be missing dashes */
/* Note: this will not work for GUIDs that have been specially encoded */
set @Project = '{' + CAST(NEWID() AS VARCHAR(36)) + '}'

select @Project

set @Project = REPLACE(REPLACE(REPLACE(@Project,'{',''),'}',''),'-','')

/* Cast as uniqueid if pattern matches, otherwise return null */ 
if @Project LIKE @HexPattern 
  select @ProjectID = CAST(
         SUBSTRING(@Project,1,8) + '-' + 
         SUBSTRING(@Project,9,4) + '-' + 
         SUBSTRING(@Project,13,4) + '-' + 
         SUBSTRING(@Project,17,4) + '-' + 
         SUBSTRING(@Project,21,LEN(@Project)-20)
         AS uniqueidentifier) 

select @ProjectID

User Defined Function in SQLServer query not working

A table-valued function returns a table. As such, just like you did in the first case where it worked, you have to put the function call after FROM. The second case fails as a table is not a column/expression to be put after SELECT.

You probably did this because your #Data table provides the function's arguments. The way to do this is CROSS APPLY:

CREATE TABLE #Data (Cols varchar(120))
INSERT INTO #Data VALUES
('James Ray,Mark will'),
('Cate,Robert Anderson, Maryy Jame williams'),
('Johnson Author, Carson')

 SELECT f.*
 FROM  #Data 
 cross apply dbo.fn_Split50(#Data.Cols, ',', DEFAULT) as f

User defined scalar-valued functions in SQLAlchemy

Scalar-valued UDFs must be called using schema-qualified name in SQL Server.

CREATE FUNCTION myfunc
(@val as INTEGER)
RETURNS INTEGER
AS
BEGIN
    RETURN
        @val + 10
END
go
select myfunc(1) --fails
go
select dbo.myfunc(1) --succeeds

So try:

select(func.dbo.myfunc(x.c.mycolumn).label("my_column_plus_10"))

per:

To call functions which are present in dot-separated packages, specify
them in the same manner:

>>> print(func.stats.yield_curve(5, 10))

stats.yield_curve(:yield_curve_1, :yield_curve_2)

sqlalchemy.sql.expression.func

Here it's a schema-qualified UDF not a "dot-separated package", but the generated SQL looks the same.

Does using a function calling GETDATE() in a view consistently give dramatically worse performance than using GETDATE() directly?

@J.Mini, you said "as if making functions with GETDATE() is always a bad idea".

It is not about the GETDATE(). It is about any user-defined scalar function in SQL Server prior to 2019. Any user-defined scalar function in SQL Server prior to 2019 is a bad idea because of likely poor performance. When your code runs 10x or 100x slower your users will notice.

This makes the standard programming idiom "if you see yourself doing the same thing many times, then make it a function with a good name" to be a bad idea in T-SQL.

Other RDBMSs like Postgres and Oracle may behave differently and work perfectly fine performance-wise with user-defined functions.

It is just a "feature" (or, rather, a peculiarity) of SQL Server that you need to be aware of. Especially since you use this function in multiple places. All of these places (queries) are likely much slower than they could have been.

Here is a good article by Aaron Bertrand on this topic:

Encapsulating Common Code Into Scalar UDFs

---

Related Topics