SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error Handling: TRY...CATCH


Error Handling: TRY...CATCH

Author
Message
5280_Lifestyle
5280_Lifestyle
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 325
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"
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16898 Visits: 19122
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
Mark Eckeard
SSC Veteran
SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)SSC Veteran (267 reputation)

Group: General Forum Members
Points: 267 Visits: 505
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



5280_Lifestyle
5280_Lifestyle
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 325
I decided to oust that code. I needed to do a homework problem using TRY/CATCH.

Thank you for all suggestions.

"Nicholas"
Alan.B
Alan.B
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5280 Visits: 7735
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



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't 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. " -- Itzek Ben-Gan 2001
wolfkillj
wolfkillj
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1480 Visits: 2582
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search