July 17, 2002 at 9:59 am
1)I have many SQL statement in my stored procedure, if a statement crash I want rollback all statements; Anybody write me an example of transactions use in stored procedure ?
2)If I don't use transactions it's possible return an error code to my application to learn the statement crashed ?
Important: my application is an ASP.NET application.
Excuse me for my bad english.
Thanks anakin.
July 17, 2002 at 10:47 am
create procedure test
as
declare @err int, @lasterr int
select @err = 0
begin transaction
statement 1
select @lasterr = @@Error
if @lasterr <> 0
select @err = 1
statement 2
select @lasterr = @@Error
if @lasterr <> 0
select @err = 1
if @err = 0
commit transaction
else
rollback transaction
Steve Jones
July 17, 2002 at 11:14 am
I prefer this way as it returns an error specific to where the problem occurred and rollsback all as soon as issue has occurred.
create procedure ip_test
as
begin transaction
statement 1
if @@Error != 0
begin
rollback transaction
raiserror('Failed on statement1, transaction rolledback', 11, -1)
return
end
statement 2
if @@Error != 0
begin
rollback transaction
raiserror('Failed on statement2, transaction rolledback', 11, -1)
return
end
commit transaction
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 18, 2002 at 3:36 am
I have tested yours replies but when I run my stored procedure from an aspx page raise an exception and raiserror and return statements are ignored; I have used an output parameter but when exception raises your value is NULL.
How can I return values to my asp.net page when exception raises ?
Other question:
In my stored procedure if a statement crash, execution must continue to the next statement, it's possible ?
Thanks for replies, anakin.
July 18, 2002 at 7:17 am
How are you retrieving the error returned? What Call are you making?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 18, 2002 at 8:44 am
can you write an example of aspx page that use your stored procedure ?
July 18, 2002 at 12:05 pm
Sorry currently I do not have .NET installed on a mchine as I am working on another project. I was wanting to look at what you have done to see if anything stands out right off.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 19, 2002 at 1:23 am
here is my page .aspx code:
................
................
Private Sub Button_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button.Click
Dim strSQL As String = "ip_test"
Dim objDbConn As SqlConnection
Dim objCommand As SqlCommand
objDbConn = New SqlConnection(ConfigurationSettings.AppSettings("strConnEnterprise"))
objDbConn.Open()
objCommand = New SqlCommand(strSQL, objDbConn)
objCommand.CommandType = CommandType.StoredProcedure
Dim a, b, c As SqlParameter
a = objCommand.Parameters.Add("@TableName", SqlDbType.VarChar, 50)
a.Direction = ParameterDirection.Input
a.Value = "test"
c = objCommand.Parameters.Add("@TableDescription", SqlDbType.VarChar, 255)
c.Direction = ParameterDirection.Input
c.Value = "test description"
b = objCommand.Parameters.Add("@result", SqlDbType.VarChar, 255)
b.Direction = ParameterDirection.Output
Dim s As Integer
Try
s = objCommand.ExecuteNonQuery()
Catch ex As SqlException
OutError.InnerHtml = "Errore " & ex.Message & " " & ex.Source
End Try
End Sub
.....................
.....................
and my stored procedure:
CREATE PROCEDURE ip_test(
@TableName varchar(50),
@TableDescription varchar(255),
@result varchar(255) OUTPUT
)
AS
declare @sql varchar(255)
DECLARE @OIDtbObjects int
set @sql = 'CREATE TABLE ' + @TableName + ' (OID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED, OID_tbBaseProducts int NOT NULL REFERENCES tbBaseProducts(OID))'
BEGIN TRANSACTION
exec (@sql)
if @@Error != 0
begin
rollback transaction
raiserror('Failed on CREATE TABLE, transaction rolledback', 11, -1)
return
end
INSERT INTO tbObjects (Name, Description) VALUES ('tbPr' + @TableName, @TableDescription)
if @@Error != 0
begin
rollback transaction
raiserror('Failed on INSERT, transaction rolledback', 11, -1)
return
end
INSERT INTO tbInheritances (OID_tbObjects, OID_tbObjects_2) VALUES (6, @OIDtbObjects )
if @@Error != 0
begin
rollback transaction
raiserror('Failed on INSERT INHERITANCES , transaction rolledback', 11, -1)
return
end
COMMIT TRANSACTION
GO
Can you modify my VB to test return code and return message of stored procedure ?
Thanks.
July 19, 2002 at 6:33 am
quote:
I have tested yours replies but when I run my stored procedure from an aspx page raise an exception...
What's the error message?
-Mike
Michael Levy
Michael Levy
ma_levy@hotmail.com
July 19, 2002 at 2:28 pm
Two issues on the above statement:
I would write the commit trans statement as
IF @@transcount>0 COMMIT TRANS
Also, you are returning a value of 0, which indicates no error. Use RETURN(-1) instead. You can use that in your calling code to know that it's an error.
July 19, 2002 at 4:54 pm
quote:
IF @@transcount>0 COMMIT TRANS
Is unneeded since we have reached the end of processing and thus an auto commit will occurr since no errors. You are just delaying processing.
As for RETURN (0 or -1) not true, when an error is raise via RAISERROR then an error is sent back like any other error. Return is for true/false checking against execution, it has nothing to do with message in regards to ADO. You can add it but does not make a difference when errors are thrown.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 19, 2002 at 5:28 pm
OK try this.
--Change--
Dim s As Integer
Try
s = objCommand.ExecuteNonQuery()
Catch ex As SqlException
OutError.InnerHtml = "Errore " & ex.Message & " " & ex.Source
End Try
--to--
Dim s As Integer
Try
s = objCommand.ExecuteNonQuery()
Catch ex As SqlException
Dim ee As SqlError, ermsg As String
For Each ee In ex.Errors
ermsg = ermsg & "Error " & ex.Message & " " & ex.Source
Next ee
OutError.InnerHtml = ermsg
End Try
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply