Error Handling in a function - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Error Handling in a function

Author

Topic

Babli
Yak Posting Veteran

53 Posts

Posted - 2007-01-22 : 05:16:14

Hi All,

I have a function which takes a column as its input and is called inside a stored procedure.

If there is any error in the function then I want that column 's value to be inserted into a table and proceed with the next record without stopping.


ALTER procedure [dbo].[mystoredproc] as
select 
	dbo.fnGetString(Column),......

fnGetString is a function in which I am doing some string manipulation and returning back a string.

How do I get hold of the value that caused the error if any??

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 05:19:51

Write a better function.
Post the code for the function here and we will investigate why you get error.

Peter Larsson
Helsingborg, Sweden

Babli
Yak Posting Veteran

53 Posts

Posted - 2007-01-22 : 05:30:28

The function will give an error when there is invalid data given tp it.


ALTER FUNCTION [dbo].[fnGetString](@inputStr VARCHAR(300))
RETURNS VARCHAR(100)
AS

RETURN right((charindex('/', reverse(@inputStr )) - 1), charindex('/', reverse(@inputStr )) - 1)

If @inputUrl doesnot contain a "/" then it will throw an error

quote:
Originally posted by Peso

Write a better function.
Post the code for the function here and we will investigate why you get error.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-22 : 05:33:40

Why don't you use RAISERROR to raise custom error when '/' is not present in the input parameter?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 05:35:06

[code]ALTER FUNCTION [dbo].[fnGetString](@inputStr VARCHAR(300))
RETURNS VARCHAR(100)
AS

RETURN case
when charindex('/', @inputStr) > 0 then right((charindex('/', reverse(@inputStr )) - 1), charindex('/', reverse(@inputStr )) - 1)
else @inputStr
end[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 05:41:08

[code]-- prepare sample data
declare @t table (data varchar(100))

insert @t
select 'c:/documents and settings/my.gif' union all
select 'sample.jpg'

-- show the result
select data,
case
when charindex('/', data) > 0 then right(data, charindex('/', reverse(data)) - 1)
else data
end
from @t[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-22 : 05:41:19

[code]ALTER FUNCTION [dbo].[fnGetString](@inputStr VARCHAR(300))
RETURNS VARCHAR(100)
AS

RETURN case
when charindex('/', @inputStr) > 0 then right(data, charindex('/', reverse(data)) - 1)
else @inputStr
end[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources