SP error trapping sent to ASP page

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)

SP error trapping sent to ASP page

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2001-09-17 : 14:10:55

Hi,
I'm building a web application and I'm trying to add some error trapping to my stored procedures. I'm a little confused on how to just return the RETURN value and not the error message (page can not be displayed). When I cause an error, the error trapping works correctly (the transaction is rolled back) but the web page still displays the database error. Is there any way to do something like "on error resume next" (VB). When I exec the sp from ISQL, the error message and return value is displayed. I only want the return value to be displayed. Here is a sample sp:
CREATE PROCEDURE mc_modTest
(
@test_data varchar(10)='',
@test2_data varchar(10)=''
)
AS
DECLARE @newkey int
DECLARE @error_test int
DECLARE @error_test2 int
SET @error_test = 0
SET @error_test2 = 0

SET NOCOUNT ON
SELECT @newkey = (SELECT ISNULL(MAX(test_key)+ 1,1) FROM tblTest)

--start transaction
BEGIN TRAN

INSERT INTO tblTest2 (test2_key,test2_data) VALUES (@newkey,@test2_data)
-- check for errors
SET @error_test2 = @@ERROR

INSERT INTO tblTest (test_key,test_data) VALUES (@newkey,@test_data)
-- check for errors
SET @error_test = @@ERROR

-- Test the error values.
IF @error_test = 0 AND @error_test2 = 0
BEGIN
-- Success. Commit the transaction.
COMMIT TRAN
END

ELSE
BEGIN
-- An error occurred. Rollback the transaction.
IF @error_test <> 0
BEGIN
ROLLBACK TRAN
RETURN(@error_test)
END
IF @error_test2 <> 0
BEGIN
ROLLBACK TRAN
RETURN(@error_test2)
END
END
SET NOCOUNT OFF
GO

Does my question make sense?

Nic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources