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
 SQL Server Development (2000)
 Error Handling Within A Cursor

Author  Topic 

rajantonyv
Starting Member

12 Posts
Posted - 2007-07-18 : 11:46:44
When I run this sql statements, I get a message saying that cursor is already open and it does an insert on one of the rows. I want it to loop through the cursor and insert the ones that it can while print to the screen the ones that cannot along with the error messages... I am overlooking something...

DECLARE CMMSCOMPRESSOR_CURSOR CURSOR
FOR
SELECT
EquipmentID,
Description,
Location,
Parent,
Vendor,
SRIALNUM,
InstallationDate,
WarrantyExpiration,
PurchasePrice,
Meter1Label,
Meter1Units,
FailureClass,
Classification,
Subclassification,
SpecificationTemplate,
MRC,
UnitNum,
MFG,
MODELNUM,
SRIALNUM1,
CNTNAME,
CNTNUM,
CFM,
MTRHP,
RPM,
TYPE,
DateMfg,
BELT
FROM DBO.COMPRESSOR
WHERE CLASSIFICATION IS NOT NULL AND SUBCLASSIFICATION IS NOT NULL
ORDER BY EQUIPMENTID

OPEN CMMSCOMPRESSOR_CURSOR

FETCH NEXT FROM CMMSCOMPRESSOR_CURSOR INTO
@EquipmentID,
@Description,
@Location,
@Parent,
@VendorID,
@SRIALNUM,
@InstallationDate,
@WarrantyExpiration,
@PurchasePrice,
@Meter1Label,
@Meter1Units,
@FailureClass,
@Classification,
@Subclassification,
@SpecificationTemplate,
@MRC,
@UnitNum,
@MFG,
@MODELNUM,
@SRIALNUM1,
@CNTNAME,
@CNTNUM,
@CFM,
@MTRHP,
@RPM,
@TYPE,
@DateMfg,
@BELTNUM

WHILE (@@FETCH_STATUS <> -1)
BEGIN

SET @MYCOMP_CLASSSTRUCTUREID = (SELECT CLASSSTRUCTUREID FROM CLASSSTRUCTURE WHERE L1CLASSIFICATIONID=@CLASSIFICATION AND L2CLASSIFICATIONID=@SUBCLASSIFICATION)

BEGIN

BEGIN TRAN

INSERT INTO EQUIPMENT
(
AVGMETERUNIT,
AVGMETERUNIT2,
BUDGETCOST,
CHANGEBY,
CHANGEDATE,
CHILDREN,
DISABLED,
EQNUM,
IMPORTDELTAMETER1,
IMPORTDELTAMETER2,
INHERITMETER1CHANG,
INHERITMETER2CHANG,
INVCOST,
ISRUNNING,
METER1WEIGHTPRCNT,
METER2WEIGHTPRCNT,
METERREADING,
METERREADING2,
ORGID,
PURCHASEPRICE,
REPLACECOST,
SITEID,
TOTALCOST,
TOTDOWNTIME,
TOTUNCHARGEDCOST,
UNCHARGEDCOST,
YTDCOST,
ASSETNUM,
BINNUM,
CALNUM,
CLASSIFICATION,
CLASSSTRUCTUREID,
DESCRIPTION,
EQ1,
EQ10,
EQ11,
EQ12,
EQ13,
EQ14,
EQ15,
EQ16,
EQ17,
EQ18,
EQ19,
EQ2,
EQ20,
EQ21,
EQ22,
EQ23,
EQ24,
EQ3,
EQ4,
EQ5,
EQ6,
EQ7,
EQ8,
EQ9,
EXTERNALREFID,
FAILURECODE,
GLACCOUNT,
INSTALLDATE,
ITEMNUM,
LDKEY,
LOCATION,
MANUFACTURER,
METERLABEL1,
METERLABEL2,
METERUNIT1,
METERUNIT2,
OWNERSYSID,
PARENT,
PRIORITY,
READINGDATE,
READINGDATE2,
ROLLOVER1,
ROLLOVER2,
ROTSUSPACCT,
SERIALNUM,
SOURCESYSID,
STATUSDATE,
VENDOR,
WARRANTYEXPDATE
)
VALUES
(
0, --AVGMETERUNIT1
0, --AVGMETERUNIT2
0, --BUDGETCOST
@MYCOMP_CHANGEBY, --CHANGEBY
@MYCOMP_CHANGEDATE, --CHANGEDATE
'N', --CHILDREN
'N', --DISABLED
@EQUIPMENTID, --EQNUM
'N', --IMPORTDELTAMETER1
'N', --IMPORTDELTAMETER2
'N', --INHERTIMETER1CHANG
'N', --INHERITMETER2CHANG
0, --INVCOST
'Y', --ISRUNNING
50, --METER1WEIGHTPRCNT
50, --METER2WEIGHTPRCNT
0, --METERREADING
0, --METERREADING2,
@MYCOMP_ORGID, --ORGID
0, --PURCHASEPRICE
0, --REPLACECOST
@MYCOMP_SITEID, --SITEID
0, --TOTALCOST
0, --TOTALDOWNTIME
0, --TOTUNCHARGEDCOST
0, --UNCHARGEDCOST
0, --YTDCOST
NULL, --ASSETNUM
NULL, --BINNUM
NULL, --CALNUM
NULL, --CLASSIFICATIONID
@MYCOMP_CLASSSTRUCTUREID, --CLASSIFICATIONSTRUCTUREID
'MFG: ' + @MFG + ', MODELNUM: ' + @MODELNUM + ', SERIALNUMBER: ' + @SRIALNUM, --DESCRIPTION
NULL, --EQ1
NULL, --EQ10
NULL, --EQ11
NULL, --EQ12
NULL, --EQ13
NULL, --EQ14
NULL, --EQ15
NULL, --EQ16
NULL, --EQ17
NULL, --EQ18
NULL, --EQ19
NULL, --EQ2
NULL, --EQ20
NULL, --EQ21
NULL, --EQ22
NULL, --EQ23
NULL, --EQ24
NULL, --EQ3
NULL, --EQ4
NULL, --EQ5
NULL, --EQ6
NULL, --EQ7
NULL, --EQ8
NULL, --EQ9
NULL, --EXTERNALREFID
@FAILURECLASS, --FAILURECODE
NULL, --GLACCOUNT
@INSTALLATIONDATE, --INSTALLATIONDATE
NULL, --ITEMNUM
NULL, --LDKEY
@LOCATION, --LOCATION
NULL, --MANUFACTURER
@METER1LABEL, --METERLABEL1
NULL, --METERLABEL2
@METER1UNITS, --METERUNIT1
NULL, --METERUNIT2
NULL, --OWNERSYSID
@PARENT, --PARENT
NULL, --PRIORITY
NULL, --READINGDATE
NULL, --READINGDATE2
NULL, --ROLLOVER1
NULL, --ROLLOVER2
NULL, --ROTSUSPACCT
@SRIALNUM, --SERIALNUMBER
NULL, --SOURCESYSID
GETDATE(), --STATUSDATE
@VENDORID, --VENDOR
@WARRANTYEXPIRATION --WARRANTY EXPIRATION DATE
)

IF @@ERROR <> 0 GOTO ERR_HANDLER

IF @PARENT <> ''

BEGIN

UPDATE EQUIPMENT SET CHILDREN='Y', CHANGEDATE=@MYCOMP_CHANGEDATE,
CHANGEBY=@MYCOMP_CHANGEBY WHERE SITEID=@MYCOMP_SITEID AND EQNUM=@PARENT

IF @@ERROR <> 0 GOTO ERR_HANDLER

END

SELECT @ROWCOUNT = COUNT(*) FROM EQTRANS WHERE ORGID=@MYCOMP_ORGID

IF @ROWCOUNT !=0
BEGIN
SET @MYCOMP_EQTRANSID = (SELECT MAX(EQTRANSID + 1) FROM EQTRANS)
END
ELSE
BEGIN
SET @MYCOMP_EQTRANSID = 1
END

INSERT INTO EQTRANS ( toloc, readingdate, toparent, tositeid, meterreading, meterreading2,
transdate, eqtransid, orgid, readingdate2, siteid, eqnum, datemoved, enterby )
values (@LOCATION, NULL, @PARENT, @MYCOMP_SITEID,0,0,GETDATE(), @MYCOMP_EQTRANSID,
@MYCOMP_ORGID, NULL, @MYCOMP_SITEID, @EQUIPMENTID, GETDATE(), @MYCOMP_CHANGEBY)

IF @@ERROR <> 0 GOTO ERR_HANDLER

EXEC dbo.MYCOMP_LOAD_EQSPEC @EQUIPMENTID, @MYCOMP_CLASSSTRUCTUREID, 'UNITNUM', '1', @MYCOMP_ORGID, NULL, NULL, @UNITNUM, @MYCOMP_CHANGEBY

IF @@ERROR <> 0 GOTO ERR_HANDLER

EXEC dbo.MYCOMP_LOAD_EQSPEC @EQUIPMENTID, @MYCOMP_CLASSSTRUCTUREID, 'MFG', '2', @MYCOMP_ORGID, NULL, NULL, @MFG, @MYCOMP_CHANGEBY

IF @@ERROR <> 0 GOTO ERR_HANDLER

EXEC dbo.MYCOMP_LOAD_EQSPEC @EQUIPMENTID, @MYCOMP_CLASSSTRUCTUREID, 'MODELNUM', '3', @MYCOMP_ORGID, NULL, NULL, @MODELNUM, @MYCOMP_CHANGEBY

IF @@ERROR <> 0 GOTO ERR_HANDLER

EXEC dbo.MYCOMP_LOAD_EQSPEC @EQUIPMENTID, @MYCOMP_CLASSSTRUCTUREID, 'SRIALNUM', '4', @MYCOMP_ORGID, NULL, NULL, @SRIALNUM, @MYCOMP_CHANGEBY

IF @@ERROR <> 0 GOTO ERR_HANDLER

EXEC dbo.MYCOMP_LOAD_EQSPEC @EQUIPMENTID, @MYCOMP_CLASSSTRUCTUREID, 'CNTNAME', '5', @MYCOMP_ORGID, NULL, NULL, @CNTNAME, @MYCOMP_CHANGEBY

IF @@ERROR <> 0 GOTO ERR_HANDLER

EXEC dbo.MYCOMP_LOAD_EQSPEC @EQUIPMENTID, @MYCOMP_CLASSSTRUCTUREID, 'CNTNUM', '6', @MYCOMP_ORGID, NULL, NULL, @CNTNUM, @MYCOMP_CHANGEBY

IF @@ERROR <> 0 GOTO ERR_HANDLER

EXEC dbo.MYCOMP_LOAD_EQSPEC @EQUIPMENTID, @MYCOMP_CLASSSTRUCTUREID, 'CFM', '7', @MYCOMP_ORGID, NULL, NULL, @CFM, @MYCOMP_CHANGEBY

IF @@ERROR <> 0 GOTO ERR_HANDLER

EXEC dbo.MYCOMP_LOAD_EQSPEC @EQUIPMENTID, @MYCOMP_CLASSSTRUCTUREID, 'MTRHP', '8', @MYCOMP_ORGID, NULL, NULL, @MTRHP, @MYCOMP_CHANGEBY

IF @@ERROR <> 0 GOTO ERR_HANDLER

EXEC dbo.MYCOMP_LOAD_EQSPEC @EQUIPMENTID, @MYCOMP_CLASSSTRUCTUREID, 'TYPE', '9', @MYCOMP_ORGID, NULL, NULL, @TYPE, @MYCOMP_CHANGEBY

IF @@ERROR <> 0 GOTO ERR_HANDLER

EXEC dbo.MYCOMP_LOAD_EQSPEC @EQUIPMENTID, @MYCOMP_CLASSSTRUCTUREID, 'DATEMFG', '10', @MYCOMP_ORGID, NULL, NULL, @DATEMFG, @MYCOMP_CHANGEBY

IF @@ERROR <> 0 GOTO ERR_HANDLER

EXEC dbo.MYCOMP_LOAD_EQSPEC @EQUIPMENTID, @MYCOMP_CLASSSTRUCTUREID, 'BELTNUM', '11', @MYCOMP_ORGID, NULL, NULL, @BELTNUM, @MYCOMP_CHANGEBY

IF @@ERROR <> 0 GOTO ERR_HANDLER

UPDATE AUTOKEY SET SEED=@EQUIPMENTID WHERE TBNAME='EQUIPMENT' AND ORGID=@MYCOMP_ORGID --AND SITEID=@MYCOMP_SITEID

IF @@ERROR <> 0 GOTO ERR_HANDLER


IF @@TRANCOUNT > 0
BEGIN
COMMIT TRAN
PRINT @CLASSIFICATION + ' ' + @SUBCLASSIFICATION + ' ' + @EQUIPMENTID + ' added successfully.'
END

RETURN

END

FETCH NEXT FROM CMMSCOMPRESSOR_CURSOR INTO
@EquipmentID,
@Description,
@Location,
@Parent,
@VendorID,
@SRIALNUM,
@InstallationDate,
@WarrantyExpiration,
@PurchasePrice,
@Meter1Label,
@Meter1Units,
@FailureClass,
@Classification,
@Subclassification,
@SpecificationTemplate,
@MRC,
@UnitNum,
@MFG,
@MODELNUM,
@SRIALNUM1,
@CNTNAME,
@CNTNUM,
@CFM,
@MTRHP,
@RPM,
@TYPE,
@DateMfg,
@BELTNUM

END

ERR_HANDLER:

BEGIN
ROLLBACK TRAN
RAISERROR ('Error occurred while loading equipment. ',16,1)
PRINT 'Error occurred on equipment ' + @EQUIPMENTID + '.'
PRINT @@ERROR
END

CLOSE CMMSCOMPRESSOR_CURSOR
DEALLOCATE CMMSCOMPRESSOR_CURSOR

Rajesh Antony Vattakunnel
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site