error handling - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

error handling

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-12-05 : 19:06:38

I have a SP like below.. I would like to put some error handling that returns error message /fail the execution whenever the length of @centers is > 800.

how can I do this? basically the length of parameters ( @centers) is greater than 800 , I would like to return the error and stop executing the SP...

ALTER proc [dbo].[usp_UnsignedOrders_Doctors] (@centers varchar(800) )
as

select
distinct(doctor_name) as doctor_name,aa.verbal_doctor as verbal_doctor
from

(
select
ve.facility_code, ve.verbal_doctor, d.doctor_name, ve.entry_type,
action_date, cast( getdate() - case when ve.action_date < ve.mod_date then ve.action_date else ve.mod_date end as int) as aging_ct --mod date is the create date normally, but can also be the modified date. In the case when mod date is greater than the action date, we are going to assume the action date is the true create date, otherwise we will assume the mod date is the create date
from ods_verbal_entry ve
join (select * from ods_doctor where doc_type = 'I') d on ve.verbal_doctor = d.verbal_code

where ve.del_flag = 'N' and entry_type <> 'ADMSVC'

)aa
left join (select verbal_doctor, max(mod_date) last_signed_dt
from ods_verbal_entry
where del_flag = 'Y'
group by verbal_doctor)ls on aa.verbal_doctor = ls.verbal_doctor

where aa.facility_code in (SELECT Item FROM dbo.Split(@centers, ','))
order by doctor_name

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-05 : 19:09:35

can't you just check the value and call RAISERROR() if it's out of bounds?

SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-12-05 : 19:27:50

can you show me som example?

I am tryin gto use try catch in sql server 2005

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-12-05 : 19:38:57

I've tried like below but it doesn;t return any error... what i am doing wrong?

alter proc [dbo].[usp_UnsignedOrders_Doctors_test] (@centers varchar(800) )
as
declare @ErrorMsgId int

if ( Len(@centers) >800)

SET @ErrorMsgID =@@ERROR
IF @ErrorMsgID <>0
BEGIN
RAISERROR ('the length of @centers is > 800' ,10,1)
END

else
select
distinct(doctor_name) as doctor_name,aa.verbal_doctor as verbal_doctor
from

(
select
ve.facility_code, ve.verbal_doctor, d.doctor_name, ve.entry_type,
action_date, cast( getdate() - case when ve.action_date < ve.mod_date then ve.action_date else ve.mod_date end as int) as aging_ct --mod date is the create date normally, but can also be the modified date. In the case when mod date is greater than the action date, we are going to assume the action date is the true create date, otherwise we will assume the mod date is the create date
from ods_verbal_entry ve
join (select * from ods_doctor where doc_type = 'I') d on ve.verbal_doctor = d.verbal_code

where ve.del_flag = 'N' and entry_type <> 'ADMSVC'

)aa
left join (select verbal_doctor, max(mod_date) last_signed_dt
from ods_verbal_entry
where del_flag = 'Y'
group by verbal_doctor)ls on aa.verbal_doctor = ls.verbal_doctor

where aa.facility_code in (SELECT Item FROM dbo.Split(@centers, ','))
order by doctor_name

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-05 : 21:09:15

try this:

if (Len(@centers) > 800) RAISERROR ('the length of @centers is > 800', 16, 1)

read BOL about raiserror and severity levels. severity level 10 is just informational, it doesn't stop execution.

SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-05 : 23:46:26

incidentally, anyone ever notice that raiserror is spelled wrong? the e does double duty.

SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-06 : 10:52:02

www.Sommarskog.se/Error-Handling-I.html
www.Sommarskog.se/Error-Handling-II.html

Madhivanan

Failing to plan is Planning to fail

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-06 : 11:23:30

quote:
Originally posted by madhivanan

www.Sommarskog.se/Error-Handling-I.html
www.Sommarskog.se/Error-Handling-II.html

both those links appear to be dead.

SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-06 : 11:26:06

that's weird - it's as if their web server is case sensitive or something. these work for me:

http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org

pootle_flump

1064 Posts

Posted - 2006-12-06 : 11:35:24

quote:
Originally posted by jezemine

incidentally, anyone ever notice that raiserror is spelled wrong? the e does double duty.

Yes - catches me out at regularly. I think there are one or two others like that too. A DBCC command is one I think...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-07 : 00:17:38

quote:
Originally posted by jezemine

that's weird - it's as if their web server is case sensitive or something. these work for me:

http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html

SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org

Really weird

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources