June 16, 2004 at 8:53 am
CREATE PROCEDURE asp_add_curriculum_content
@Curriculum_ID INT = NULL,
@Content_Category_ID INT = NULL,
@Title varchar(255) = NULL,
@ConSequence INT = NULL,
@CatSequence INT = NULL,
@Content_Type_ID INT = NULL,
@FrenchTitle varchar(255),
@return2 varchar(50) output
AS
set nocount on
BEGIN TRAN
BEGIN
DECLARE @Content_ID INT
INSERT INTO Content(Title,Content_Type_ID, F_Title)
VALUES(@Title,@Content_Type_ID,@FrenchTitle)
if @@Error <> 0
begin
ROLLBACK TRAN
set @return2 = 'rollback'
end
SET @Content_ID = @@IDENTITY
INSERT INTO curriculum_content(Cur_ID,Content_ID,[Sequence])
VALUES(@Curriculum_ID,8975/*@Content_ID*/,@ConSequence)
if @@Error <> 0
begin
ROLLBACK TRAN
set @return2 = 'rollback'
end
INSERT INTO content_category_details(content_id, content_category_id, [Sequence])
VALUES(@content_id, @Content_Category_ID,@CatSequence)
if @@Error <> 0
begin
ROLLBACK TRAN
set @return2 = 'rollback'
end
If @@Error = 0
Begin
COMMIT TRAN
set @return2 = 'commit'
End
Else
Begin
ROLLBACK
set @return2 = 'rollback'
End
END
GO
--------------------------------------------------
In case there are no errors in my ASP code I get the value of
the output parameter @return2, no problem.
But then I break my SP on purpose:
INSERT INTO curriculum_content
(Cur_ID,Content_ID,[Sequence])
VALUES(@Curriculum_ID,8975/*@Content_ID*/,@ConSequence)
[Content_ID 8975 does not exist in parent table]
and ASP code collapses throwing
"INSERT statement conflicted with COLUMN FOREIGN KEY..." error on the web page.
My goal is not to show any SQL errors at the front end but to take control of the process, capture the
transaction error and display a nicely formatted HTML TRANSACTION ERROR.
June 21, 2004 at 8:42 am
http://www.sommarskog.se/error-handling-II.html
http://www.sommarskog.se/error-handling-I.html
should help
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply