Solved

Raising an Error in a Case Statement

Posted on 2007-11-19
13
182 Views
Last Modified: 2010-03-20
In a Case Statement, I need to raise an error if this scenario hoppens. The question is how do I raise the error in the case statement?  In this Case an Error will happen.

SELECT @startDate = CASE
WHEN
RP.Current_Run_Date > @maxdate
 THEN 'RaiseError Here'
END
from Reports.dbo.tblRpt_Param RP
0
Comment
Question by:donnie91910
13 Comments
 
LVL 15

Expert Comment

by:mcmonap
ID: 20316621
If you want to raise an error for each record where the Current_Run_Date is greater than @maxdate I think you will need to process each record seperately.

I think it would make more sense to:
a) exclude these records from this processing with "WHERE RP.Current_Run_Date >= @maxdate"
b) process these error records seperately and deal with them there.

Is that possible?
0
 

Author Comment

by:donnie91910
ID: 20316746
Not sure what you mean or how to do it.
0
 

Author Comment

by:donnie91910
ID: 20316789
Is it not possible to Raise an Error in the Case statement?
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 15

Expert Comment

by:mcmonap
ID: 20316824
Nope. You cannot raise an error that way.

Please describe what you want to achieve and provide some data as an example if you have some to hand.
0
 

Author Comment

by:donnie91910
ID: 20316898
I've tried this:
DECLARE @currentDate varchar(10)
DECLARE @startdate datetime
DECLARE @AsOfDate datetime
DECLARE @maxdate datetime

SELECT @maxdate = max(crtDate) from [Transaction]..NARRStatusHistory  

SELECT @startDate = CASE
WHEN
RP.Current_Run_Date > @maxdate
 THEN RAISERROR ('Current Run Date is Greater than Max Date', 16, 1)
END
from Reports.dbo.tblRpt_Param RP
Where RP.ReportName = 'AXA_Approved_Accts'

I get these errors:
Server: Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'RAISERROR'.
Server: Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'END'.
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 20316942
Hi donnie91910

I see what you  are trying to do - it won't work like this.  Please explain what you want to achieve. Why do you want to raise an error?

What is wrong with this sample or something like it?  You are not explaining what you actually _want_ to achieve, you could raise an error for each record that is greater than @maxdate but what are you going to do with those errors?  there could be a hundred - will you write them to the sql log, output them into a log file?

What is your aim?
DECLARE @currentDate varchar(10) 
DECLARE @startdate datetime
DECLARE @AsOfDate datetime
DECLARE @maxdate datetime
 
SELECT @maxdate = max(crtDate) from [Transaction]..NARRStatusHistory  
 
SELECT @startDate = CASE
WHEN
RP.Current_Run_Date > @maxdate 
 THEN 'Current Run Date is Greater than Max Date'
END
from Reports.dbo.tblRpt_Param RP
Where RP.ReportName = 'AXA_Approved_Accts'

Open in new window

0
 

Author Comment

by:donnie91910
ID: 20317796
Basically what I am trying to achive is that when the code hits an error such as the
RP.Current_Run_Date > @maxdate  
then the code should quit.  
Here is where my confusion comes in; I'm not sure how to, after the code quits to let the operator  know what the error is that made the code quit.
The only time that I want the code to quit is when RP.Current_Run_Date > @maxdate.  
I am attaching the whole query that will eventually be made into a Stored Procedure.  Where the line is commented out as:
--Error Code
SELECT @startDate = CASE
WHEN
RP.Current_Run_Date > @maxdate
 THEN RAISERROR ('Current Run Date is Greater than Max Date', 16, 1)
END
from Reports.dbo.tblRpt_Param RP
Where RP.ReportName = 'AXA_Approved_Accts'

The code above is giving me the error:
Server: Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'RAISERROR'.
Server: Msg 156, Level 15, State 1, Line 35
Incorrect syntax near the keyword 'END'.

Any help is greatly appreciated!

DECLARE @currentDate varchar(10) 
DECLARE @startdate datetime
DECLARE @AsOfDate datetime
DECLARE @maxdate datetime
 
SELECT @maxdate = max(crtDate) from [Transaction]..NARRStatusHistory  
 
SELECT @startDate = CASE
WHEN
RP.Current_Run_Date = @maxdate 
THEN RP.Previous_run_Date
END
from Reports.dbo.tblRpt_Param RP
Where RP.ReportName = 'AXA_Approved_Accts'
 
-----------
SELECT @startDate = CASE
WHEN
RP.Current_Run_Date < @maxdate 
 THEN RP.Current_Run_Date
END
from Reports.dbo.tblRpt_Param RP
Where RP.ReportName = 'AXA_Approved_Accts'
 
--Error Code
SELECT @startDate = CASE
WHEN
RP.Current_Run_Date > @maxdate 
 Then RAISERROR ('Current Run Date is Greater than Max Date', 16, 1)
END
from Reports.dbo.tblRpt_Param RP
Where RP.ReportName = 'AXA_Approved_Accts'
 
--****************************************************************
select 
	convert(varchar(25),n.CrtDate,120) [CrtDate]
	, n.NewNARRStatusID
	, n.AccountID
	, replace(a.Name1,',',' ') [Name1]
	, replace(a.Name2,',',' ') [Name2]
	, replace(a.Name3,',',' ') [Name3]
	, replace(a.Name4,',',' ') [Name4]
	, replace(a.Name5,',',' ') [Name5]
 	, convert(varchar(10),a.AddDate,120) [AddDate]
	, a.SubsidiaryNo
	, a.CorrBD
	
	--into RollBackDB.dbo.tmpAXA_ApprovedAccts_1112
 
from 	[transaction].dbo.narrstatushistory n (nolock)
	join BETA..vw_Beta_Acct_Mstr a (nolock) on a.accountno = n.accountid
	
where 	
--	n.crtdate > select convert(varchar(10),getdate(), 120)
 
	n.crtdate between convert(varchar(10),getdate() - 4, 120) + ' 22:00:00.000' and convert(varchar(10),getdate() - 1, 120) + ' 21:59:59.000'
	and n.newnarrstatusid = '32'
 	AND (a.AccountNo  in( SELECT NH.AccountID		
					  FROM [Transaction]..NARRStatusHistory NH 
					  WHERE NH.NewNARRStatusId = 32
			AND convert(varchar(10), NH.CrtDate, 120) > @startdate) --and @currentdate)
		   OR a.CorrBD <> 'AXA')
 
 --don't use NARR if not AXA
 
order by n.crtdate

Open in new window

0
 
LVL 18

Expert Comment

by:JR2003
ID: 20327654
Can't you just add the criteria to your sql where clause so it does not do anything with it?:

WHERE RP.Current_Run_Date <= @maxdate.
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 20331351
Hi donnie91910,

You want to tell the operator there is a problem with a row - how are you planning to display this?  Do you actually need to raise an error?  It sounds like you are passing these results back to an application that then processes them.  you do not need to raise an error in SQL to do this.  All you need to do is mark the records in someway and then code the application to deal with the marked records in a way the operator understands.
0
 

Author Comment

by:donnie91910
ID: 20340770
Because of the limitations of the "Case Statement" I managed to use an "If Statement" for the Raise Error.
At which point when the Raise Error happens, then the process stops and the Raise Error, because it is running from a Job then the message displays in the Job.
Thanks in advance for the help.
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 20342185
Hi donnie91910,

The only thing that may be a problem here is that if you have 100 bad rows in your data then the process must be run 101 times before you will get your report.

Good that you have a solution that works for you though.
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 20953213
PAQed with points refunded (500)

Computer101
Community Support Moderator
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 31
Add different cell to otherwise similiar row 4 39
Sql server function help 15 31
SQL Server: Unable to remove duplicate sets in Header/Detail 6 25
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

822 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question