October 5, 2004 at 9:08 pm
Is there a way to perform error trapping in a UDF as shown below? The RAISERROR Statement produces a compile error of 'Invalid use of RAISERROR in a Function"
Create Function dbo.FnGetDateDiffs2
(@StartDate DATETIME, @EndDate DATETIME)
RETURNS @retElapsed TABLE (Days INT, Months INT, Years INT)
AS
BEGIN
IF @EndDate < @StartDate
BEGIN
RAISERROR("Invalid Date Range", 16, 1)
END
ELSE
DECLARE @Days INT, @Months INT, @Years INT
SELECT @Days = (SELECT DATEDIFF(dd, @StartDate, @EndDate))
SELECT @Months = (SELECT DATEDIFF(mm, @StartDate, @EndDate))
SELECT @Years = (SELECT DATEDIFF(yy, @StartDate, @EndDate))
INSERT @retElapsed SELECT @Days, @Months, @Years
RETURN
END
October 5, 2004 at 9:28 pm
This link is relevant but not good news. I think you might have to recast as a stored proc.
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 5, 2004 at 11:18 pm
October 5, 2004 at 11:23 pm
I wouldn't bet against it - some bright guys out there.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 7, 2004 at 9:45 am
That's my major beef with UDF's.
The other is that input parameters can be specied with default values, but they are really all required! And you can't use GETDATE() without going through a silly view to get its value. And, can't run the UDF in the debugger?
If I get invalid parameters for a UDF I return either a NULL value or specific values for trapped errors. If your return value is a date, you don't have many options for returning descriptive values denoting errors. I've written a lot of date related UDF's. If 2 date parameters are passed in the wrong order I've been swapping the values of them. So if start/end date parameters are switched I kindly handle switching them within the UDF and doing the computation.
Still even with these limitations, because UDF's can be invoked in SELECT statements it makes them easier to test/implement than sp's.
Jeff
October 12, 2004 at 7:33 am
Not really done much with UDF's or returning tables from them but you could return the error in the output table like this
Create Function dbo.FnGetDateDiffs2
(@StartDate DATETIME, @EndDate DATETIME)
RETURNS @retElapsed TABLE (Days INT, Months INT, Years INT, ErrorNum INT, ErrorMsg VARCHAR(100))
AS
BEGIN
DECLARE @Days INT, @Months INT, @Years INT, @ErrorNum INT, @ErrorMsg VARCHAR(100)
IF @EndDate < @StartDate
BEGIN
SET @ErrorNum = 1
SET @ErrorMsg = 'Invalid Date Range'
END
ELSE
BEGIN
SELECT @Days = (SELECT DATEDIFF(dd, @StartDate, @EndDate))
SELECT @Months = (SELECT DATEDIFF(mm, @StartDate, @EndDate))
SELECT @Years = (SELECT DATEDIFF(yy, @StartDate, @EndDate))
SET @ErrorNum = 0
SET @ErrorMsg = ''
END
INSERT @retElapsed SELECT @Days, @Months, @Years, @ErrorNum, @ErrorMsg
RETURN
END
This is probably not a usable solution (as would be returning null values) as it would complicate the outer processing.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply