Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error Handling: TRY...CATCH Expand / Collapse
Author
Message
Posted Monday, August 13, 2012 12:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 12:49 PM
Points: 39, Visits: 278
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"
Post #1344359
Posted Monday, August 13, 2012 1:31 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:28 PM
Points: 3,635, Visits: 8,147
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1344386
Posted Monday, August 13, 2012 4:54 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 5:24 AM
Points: 128, Visits: 490
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



Post #1344483
Posted Monday, August 13, 2012 5:08 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 12:49 PM
Points: 39, Visits: 278
I decided to oust that code. I needed to do a homework problem using TRY/CATCH.

Thank you for all suggestions.


"Nicholas"
Post #1344490
Posted Thursday, August 16, 2012 3:23 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 5:07 PM
Points: 590, Visits: 2,757
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



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1346269
Posted Monday, August 20, 2012 9:40 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:06 AM
Points: 1,061, Visits: 2,565
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
Blog: SQLSouth
Twitter: @SQLSouth
Post #1347289
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse