error trap routine

Last Post 25 Aug 2009 11:23 AM by timcronin. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

25 Aug 2009 11:23 AM
I have a sql script which is supposed to load rows to a remote db. It has been getting an error (which it should), the problem is instead of ending the script it updates the selected records as sent (which it did not because of the error)

Here is the script, and below it the error it returns, must be having a brain lock

select newid() as cms_order_num,l.costcenter,7 as requesttype,
left(l.siteid,5) as instid,left(l.siteid,5) as siteid,
prescriptionnumber as rxno,f.pharmid,refilldate as insert_date, 'n' as rec_status,
into #temprefills
from refill r
inner join location l on = r.locationid
inner join servername.fac.dbo.facilities f on f.facid = l.costcenter
where sendtopharmacy = 1
and senttopharmacy = 0
--load to pharmacy
declare @error int
set @error = @@error
INSERT INTO servername.[Pharm].[dbo].[cms_refillimport]
select * from #temprefills
print @@error
if @@error <> 0
goto exitsp


--update processed records
print 'should not be here'
update refill
set senttopharmacy = 'True'
where sendtopharmacy = 1 and prescriptionnumber in (select rxno from #temprefills)
drop table #temprefills

print 'leaving'
drop table #temprefills



(4 row(s) affected)
Msg 248, Level 16, State 1, Line 16
The conversion of the varchar value '6287684900001' overflowed an int column. Maximum integer value exceeded.
The statement has been terminated.
should not be here

(4 row(s) affected)
Msg 3701, Level 11, State 5, Line 45
Cannot drop the table '#temprefills', because it does not exist or you do not have permission.

Acceptable Use Policy