April 24, 2007 at 3:26 am
If the procedure (testUnique ) is executed from another procedure, eg: launchtestUnique
CREATE PROCEDURE testUnique
AS
declare @strSQLExec varchar (1000)
set @strSQLExec = 'insert into a select * from a_temp tempT where a_pk NOT IN (select a_pk from a)'
print @strSQLExec
EXEC (@strSQLExec)
RETURN (0)
GO
and there is an error during sql statement insert into a select *..., (for instance a colums in the 'a' table has a constraint for unique data) the procedure goes on, the error is only written!!!!!!.
I would like to stop the execution when the error occurs.
(I resolved it writing before the SQL insert into a select.... the statement SET XACT_ABORT ON, eg:
set @strSQLExec ='set xact_abort ON insert into a select * from a_temp where a_pk NOT IN (select a_pk from a) SET XACT_ABORT OFF)'.
This works, but there is another way?
Thank
April 24, 2007 at 4:09 am
In SQL2000, you need to monitor @@ERROR after each statement. With your example, in testUnique do something like:
EXEC (@strSQLExec)
RETURN(@@ERROR)
and in launchtestUnique do something like:
DECLARE @Err int
EXEC @Err = dbo.testUnique
IF @Err <> 0
With SQL2005 TRY/CATCH can be used.
I do not think it is a good idea to change the state of XACT_ABORT.
April 24, 2007 at 4:43 am
did you try ...
CREATE PROCEDURE testUnique
AS
declare @strSQLExec nvarchar (1000)
set @strSQLExec = N'select * from NonExisting_object '
print @strSQLExec
DECLARE @myrc AS INTEGER
SET @myrc = 0
EXECUTE @myrc =sp_executesql @strSQLExec
IF @myrc <> 0
-- faultprocedure
PRINT @myrc
RETURN (@myrc)
return
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 24, 2007 at 7:08 am
Thank for everybody,
Alzdba solution works.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply