Error handling with a function? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error handling with a function?

Author

Topic

malachi151
Posting Yak Master

152 Posts

Posted - 2011-03-29 : 12:28:30

What is the best way to handle errors within a SQL function?

It seems to be virtually impossible to do so. I figured that RAISEERROR would work, but that isn't even allowed.

I'm trying to create functions to handle conversion of character values to numeric or date ones, that will handle converting blank spaces to NULL instead of 0 and will error out when there is a conversion error, reporting the value and column that caused the error.

Here was my shot (after a few attempts using TRY CATCH first). Is there any way to accomplish the intended goal here?

ALTER FUNCTION [dbo].[StringToInt](@value varchar(200), @column_id varchar(200) = '')
RETURNS int
AS
BEGIN
	IF ISNUMERIC(@value) = 0
	BEGIN
		RAISERROR ('%s in column %s is not numeric', 10, 1, @value, @column_id);
	END
	RETURN (CASE WHEN LTRIM(RTRIM(@value)) = '' THEN NULL ELSE CAST(LTRIM(RTRIM(@value)) as int) END);
END

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-29 : 12:34:52

User-defined functions have to return a value or set of values, they cannot throw errors and return nothing. My suggestion would be to have something like:

ALTER FUNCTION [dbo].[StringToInt](@value varchar(200), @column_id varchar(200) = '')
RETURNS int
AS
BEGIN
	RETURN (CASE WHEN ISNUMERIC(@value) = 1 AND LTRIM(RTRIM(@value)) <> '' THEN CAST(LTRIM(RTRIM(@value)) as int) ELSE NULL END);
END

If you really need to handle that condition as an error you'll be limited to a stored procedure.

malachi151
Posting Yak Master

152 Posts

Posted - 2011-03-29 : 12:45:37

Well that doesn't do anything, it will just set non-numeric values to NULL, which is defiantely not wanted, an error has to be raised so it can be addressed.

A stored procedure won't work in this case.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-03-29 : 12:50:23

You don't raise errors in functions (as noted by robvolk), rather you trap an error (or program defensivly so you don't pass bad values to a function) in the calling environment.

ALTER FUNCTION [dbo].[StringToInt](@value varchar(200), @column_id varchar(200) = '')
RETURNS int
AS
BEGIN
	RETURN (CASE WHEN LTRIM(RTRIM(@value)) = '' THEN NULL ELSE CAST(LTRIM(RTRIM(@value)) as int) END);
END

PS - Incase you didn't know ISNUMERIC is not sufficient to check for numeric values.

malachi151
Posting Yak Master

152 Posts

Posted - 2011-03-29 : 14:44:56

quote:
Originally posted by Lamprey

You don't raise errors in functions (as noted by robvolk), rather you trap an error (or program defensivly so you don't pass bad values to a function) in the calling environment.

ALTER FUNCTION [dbo].[StringToInt](@value varchar(200), @column_id varchar(200) = '')
RETURNS int
AS
BEGIN
RETURN (CASE WHEN LTRIM(RTRIM(@value)) = '' THEN NULL ELSE CAST(LTRIM(RTRIM(@value)) as int) END);
END

PS - Incase you didn't know ISNUMERIC is not sufficient to check for numeric values.

Yeah, I know I was just using it since I couldn't use TRY .. CATCH.

This is essentially unsatisfactory. I'll try using CLR functions, maybe I'll be able to do more there, but I'm not certain that I can handle errors in a CLR function either.

The whole point of a function is to be able to simplfy the code. Of course I can do the check in the calling code, but when converting hundreds of columns of data that would require wrapping every column in a huge string of code, which is exactly what I'm trying to avoid...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-29 : 15:56:28

quote:
This is essentially unsatisfactory.

I don't understand this viewpoint. SQL is a declarative data manipulation language, not a general purpose procedural language. Data "errors" (data not a valid number) are not the same as procedural errors (open a file that doesn't exist). The fact that UDF's can't perform error handling is not a problem or shortcoming.

If you're trying to find invalid data, you don't need a function or stored procedure, use a query instead:

SELECT 'myTable' table_name,
'myColumn' column_name,
myColumn value,
ID  -- this is the primary key of the table, and doesn't have to be included
FROM myTable
WHERE IsNumeric(myColumn)=0

This can be generated for any number of tables and columns:

SELECT 'SELECT ID, ' + QUOTENAME(table_name,'''') + ' table_name, ' + 
QUOTENAME(column_name,'''') + ' column_name, ' + QUOTENAME(column_name) + 
' value FROM ' + QUOTENAME(table_name) + ' WHERE IsNumeric(' + QUOTENAME(column_name) + ')=0'
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME IN ('myTable')  -- add tables to list, or provide other condition
AND DATA_TYPE LIKE '%char'  -- modify condition for specific columns

...and even executed automatically using dynamic SQL.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-03-29 : 16:39:52

Maybe your example was too simple and doesn't articulate the real-world scenario? Otherwise, how is generating an error (cannot convert to INT) different then knowing a value will not convert to INT and throwing an error?

malachi151
Posting Yak Master

152 Posts

Posted - 2011-03-29 : 17:47:03

quote:
Originally posted by Lamprey

Maybe your example was too simple and doesn't articulate the real-world scenario? Otherwise, how is generating an error (cannot convert to INT) different then knowing a value will not convert to INT and throwing an error?

The advantage of the function is that it would tell you the column and value that generated the error instead of just saying something like "String or binary data would be truncated" which isn't all that helpful when you have 500 columns and millions of rows. Of course I could use something more robust like SSIS with error handling, but I was trying for a quick and dirty solution here.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources