error trap routine

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

--
25 Aug 2009 12:23 PM
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

begin
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,
f.mardbname
into #temprefills
from refill r
inner join location l on l.id = 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]
([cms_order_num]
,[facID]
,[requesttype]
,[instid]
,[siteid]
,[rxno]
,[pharmid]
,[insert_date]
,[import_rec_status]
,[mardbname])
select * from #temprefills
print @@error
if @@error <> 0
begin
goto exitsp

end


--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

exitsp:
print 'leaving'
drop table #temprefills
return

end

-------------------------------------------------------------------
error


(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.
248
should not be here

(4 row(s) affected)
leaving
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
---