November 3, 2005 at 8:28 am
Hello all!
I am new to the the SQL Server environment. My background is Oracle 8i and 9i.
I am attempting to trap errors in a stored proc and return a resultset of those errors to the calling program. (In my case a ColdFusion page.) I am doing a batch insert/delete of records be receiving XML in a TEXT IN parameter. I can't get it to return the error. It just craps out if it finds an error. Any help is appreciated!
Here is the code:
CREATE PROCEDURE cpdsp_UpdateCompanyReportCategory
@queryXML TEXT
AS
DECLARE @idoc INT, @nReportCategoryID INT, @nCompanyID INT, @nTRXType VARCHAR(1)
-- temp table for errors
DECLARE @errorTable TABLE (nErrorCode INT, cErrorMessage VARCHAR(1000))
EXEC sp_xml_preparedocument @idoc OUTPUT, @queryXML
DECLARE CUR_CRC CURSOR READ_ONLY FOR
SELECT NREPORTCATEGORYID, NCOMPANYID, TRXTYPE
FROM OPENXML (@idoc, '/CPD_TRANSACTION/CPD_REC',2)
WITH (NREPORTCATEGORYID INT,
NCOMPANYID INT,
TRXTYPE VARCHAR(1))
OPEN CUR_CRC
FETCH NEXT FROM CUR_CRC INTO
@nReportCategoryID, @nCompanyID, @nTRXType
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@nTRXType = 'I')
INSERT INTO cpdtb_CompanyReportCategory
(nReportCategoryID, nCompanyID)
VALUES
(@nReportCategoryID, @nCompanyID)
ELSE
DELETE FROM cpdtb_CompanyReportCategory
WHERE nReportCategoryID = @nReportCategoryID
AND nCompanyID = @nCompanyID
IF @@ERROR <> 0
INSERT INTO @errorTable
(nErrorCode, cErrorMessage)
VALUES
(@@ERROR, 'Error adding/removing Section ID ' + str(@nReportCategoryID) + ' , and Company ID ' + str(@nCompanyID))
FETCH NEXT FROM CUR_CRC INTO
@nReportCategoryID, @nCompanyID, @nTRXType
END
CLOSE CUR_CRC
DEALLOCATE CUR_CRC
SELECT nErrorCode, cErrorMessage
FROM @errorTable
GO
November 3, 2005 at 8:34 am
Check out http://www.sommarskog.se
There you find two articles about error handling in SQL Server.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply