August 25, 2009 at 12:58 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.
August 25, 2009 at 1:35 pm
well, personally never used GOTO in a stored proc, so i can't comment on that, but try this. Note that it requires SQL Server 2005+, as the TRY-CATCH blocks don't exist in prior versions.
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
BEGIN TRY
INSERT INTO servername.[Pharm].[dbo].[cms_refillimport]
(
[cms_order_num],
[facID],
[requesttype],
[instid],
[siteid],
[rxno],
[pharmid],
[insert_date],
[import_rec_status],
[mardbname]
)
-- Don't think this select is necessary for anything, probably should remove it --
select *
from #temprefills
END TRY
BEGIN CATCH
print 'leaving'
drop table #temprefills
RETURN 0
END CATCH
--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
end
August 25, 2009 at 2:27 pm
Guess what happens when you print @@error? It resets to 0 because the PRINT statement executed successfully. Try commenting that out.
Best practice is to always assign @@Error to a variable and then test the variable. It keeps booboos like that from biting you.
declare @x int
declare @err int
set @x = 6287684900001 -- force error
set @err = @@ERROR -- trap the value of @@ERROR
-- (@@ERROR gets set to 0 by the successful execution of the above statement)
select @@ERROR as [@@ERROR], @err as [@err] -- see the difference?
if @err = 0 goto DAD
-- if @@ERROR = 0 goto MOM
MOM:
select 'Hi Mom'
return
DAD:
select 'Hi Dad'
return
Cannot drop the table '#temprefills', because it does not exist or you do not have permission.
You're always going to see this, until you have something in place at the end of your update session that either says return, or says to skip that exitsp section. It's not like you are branching to subroutines.
Definitely take the time to read up on TRY/CATCH logic.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 26, 2009 at 7:52 am
Actually the select statement you told me to remove inputs the data referenced with the insert
August 26, 2009 at 9:35 am
print @@error ????
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply