March 31, 2010 at 12:00 pm
I want to return my own error message and hide what SQL is returning.
Can someone please help me.
Thank you
Wayne
Example code:
DECLARE @xx AS DATETIME
SELECT @xx = CONVERT(DATETIME, '2010-02-31 00:00:00', 102)
IF @@ERROR <> 0
BEGIN
SELECT 'Only 28 days in Feb not 31'
END
Results:
Msg 242, Level 16, State 3, Line 6
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
(1 row(s) affected)
Wayne Benhart
March 31, 2010 at 12:10 pm
You are looking for something more like this:
declare @xx as datetime;
begin try
select @xx = convert(datetime, '2010-02-31 00:00:00', 102);
end try
begin catch
select 'only 28 days in February';
end catch
Not necessarily how I would actually write the code, but it does what you want.
March 31, 2010 at 12:22 pm
Is there a better way to handle to do this?
Wayne Benhart
March 31, 2010 at 12:27 pm
It depends. There is no context to put this in. Also, if this is user entry via a GUI, the error checking should occur there not in the database.
March 31, 2010 at 12:51 pm
That's the best way I know to handle it within the database. Just remember, if the error is of severity high enough to cause a disconnect, the CATCH can't catch the error, so it will still go back to the client as a raw SQL error.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 31, 2010 at 5:37 pm
Thank you very much for you help.
Wayne
Wayne Benhart
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply