Error Handling: TRY...CATCH

Question

Post reply

Error Handling: TRY...CATCH

SqlHomebrewer

SSC Eights!

Points: 874
More actions
August 13, 2012 at 12:49 pm

#262869

I'm trying to write an error handling query using the TRY...CATCH construct as a homework problem. I want to compare today's day to a variable @CurrentDay equal to a day of 24 and have it fail out of the TRY construct to the CATCH construct and print a message indicating the actual day.
Is it possible to make my query work? If it is, what changes do I need to make my query work?
DECLARE @CurrentDay AS INT = 24;
BEGIN TRY
SELECT @CurrentDay = DAY(CURRENT_TIMESTAMP)
PRINT 'Today is the 24th day of the month';
END TRY
BEGIN CATCH
PRINT 'Today is day number ' + DAY(CURRENT_TIMESTAMP) + ' of the month';
END CATCH
Nicholas
"Nicholas"

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

You should use an IF...ELSE... statement if you want to evaluate a condition.

With your query you're assigning the value to the variable, not comparing. It should be like this.

IF @CurrentDay = DAY(CURRENT_TIMESTAMP)

PRINT 'Today is the 24th day of the month'

After that, you should use the ELSE and generate an error. I will leave this to investigate and come back and ask for specific questions.

Here's a guide:

http://msdn.microsoft.com/en-us/library/ms178592.aspx

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Mark Eckeard Hall of Fame Points: 3462 More actions · Answer 2

Like Luis stated, use an if/else. If it fails, it'll fall to the "else" part and you can produce your message. Try/catch would normally be used to trap any unforeseen errors.

Mark

SqlHomebrewer SSC Eights! Points: 874 More actions · Answer 3

I decided to oust that code. I needed to do a homework problem using TRY/CATCH.

Thank you for all suggestions.

"Nicholas"

Alan Burstein SSC Guru Points: 61136 More actions · Answer 4

TRY/CATCH is traditionally used more for stuff like this:

SET NOCOUNT ON;

DECLARE @pos TABLE (sale_id varchar(4), product_id int)

DECLARE @err varchar(200)

BEGIN TRY

INSERT INTO @pos VALUES ('ccc', 'a')

END TRY

BEGIN CATCH

-- update the log file

SET @err=ERROR_MESSAGE()

IF ERROR_NUMBER() = 245

SET @err = @err+'(Still getting bad data from Joe''s ETL)'

PRINT @err

END CATCH

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 5

5280_Lifestyle (8/13/2012)
I'm trying to write an error handling query using the TRY...CATCH construct as a homework problem. I want to compare today's day to a variable @CurrentDay equal to a day of 24 and have it fail out of the TRY construct to the CATCH construct and print a message indicating the actual day.
Is it possible to make my query work? If it is, what changes do I need to make my query work?
DECLARE @CurrentDay AS INT = 24;
BEGIN TRY
SELECT @CurrentDay = DAY(CURRENT_TIMESTAMP)
PRINT 'Today is the 24th day of the month';
END TRY
BEGIN CATCH
PRINT 'Today is day number ' + DAY(CURRENT_TIMESTAMP) + ' of the month';
END CATCH
Nicholas

If your homework isn't overdue yet, I suggest that you should investigate using an IF construct in your TRY block in conjunction with RAISERROR to generate an error of sufficient severity to initiate error handling (i.e., shift control to the CATCH block) when the date you are testing does not fall within the required value(s). That should get you started, but if you run into problems, post the code you've tried and we'll see what we can do.

Jason Wolfkill