Error Handling!!! - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Error Handling!!!

Author

Topic

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-08 : 11:00:02

Hi all,

I am calling some stored procedures from ASP. These strored procedures have to deal with lots of deletes and updates. So i have thought of implementing transaction commits and rollbacks. But if a rollback occurs in these stored procedures, i want to get a value back to asp page, based on this value i will run the next stored procedure.

Can anybody give me some examples for doing this.

Somebody please help me with this!!!!!!!!!

Thanks a lot in advance,
Nitu

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-08 : 11:09:43

have a stored procedure use an output parameter that you set to what you wish when you rollback.
you can also set the return value of the sproc to some number or simply do "select yournumberHere" and then exit the proc.

Go with the flow & have fun! Else fight the flow

Nitu
Yak Posting Veteran

81 Posts

Posted - 2006-03-08 : 11:53:45

lets have an example,

create spupdaterecords
@input1 int
as
Declare @errorCount
begin tran

if @input1 = 1
update table set field = 0
else
update table set field = -1

if @@error <> 0
Begin
rollback tran
select @errorCount = @@error
End
Commit tran
Go

------------------------------------------------
asp
------------------------------------------------

set rs = createobject("adodb.recordset")
sql = "spupdaterecords 1"
rs.open sql, conn

----------------------------
now this is how iam executing the stored procedure, now when i say

response.write rs("errorCount")

its giving me an error, saying object is not found in the stored procedure.

how can i get this value in asp

Thanks a lot in advance

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-08 : 12:30:37

select @errorCount = @@error
if @errorCount <> 0
Begin
rollback tran
select @errorCount
return
End
Commit tran

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-09 : 01:03:34

More on Error handlings
http://www.sommarskog.se/error-handling-I.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources