<div>
try this: 
 
 
begin try 
&nbsp; begin transaction 
&nbsp; 
&nbsp; insert 
&nbsp; insert 
&nbsp; ... 
&nbsp; insert 
&nbsp; commit 
end try 
begin catch 
&nbsp;rollback 
end catch
</div>

<div>
Quite correct, wrap it up in a Begin Transaction... Commit Transaction block. 
 
the try...catch block is good to capture a &quot;hard&quot; error, or, you could set a variable to capture &quot;logic&quot; errors and check that variable at the end... 
 
declare @err int 
 
set @err = 0 
 
begin try 
&nbsp; begin transaction 
&nbsp; 
&nbsp; if &nbsp; set @err = @err + 1 
&nbsp; else insert 
&nbsp; 
&nbsp; if &nbsp; set @err = @err + 1 
&nbsp; else insert 
&nbsp; ... 
&nbsp; if &nbsp; set @err = @err + 1 
&nbsp; else insert 
 
&nbsp; if @err = 0 &nbsp;commit 
&nbsp; else begin 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;rollback 
&nbsp; end 
 
end try 
begin catch 
&nbsp;rollback 
end catch
</div>

<div>
Thanks allot for he help. 
Ok so if i understand it, i must use the 1 Begin Transaction for all the Inserts and only at the end if there are no errors then commit? 
 
Mark, can u give me a little more help on this 
&gt;&gt;if &nbsp; set @err = @err + 1 
what or how would i use a logical condition? do u perhaps have a sample so i can just get an idea of how u use it please? 
 
I am executing this with a windows service so there is no interface to report an error. I was thinking i can write an error that occured to a table and the automatically send an email to the user that it falied with the error message, So i would then setup the SP to return a string object - is that ok to do it that way? 
 
thanks
</div>

<div>
Morning Guys, 
Thanks allot for the help 
:)
</div>

<div>
<div class="content wysiwyg-content">
Hi, 
What is the best way to hanlde error handling in a sp? 
 
I have a 11 Insert Statements that follow one after the other, how do i reverse my hole process if oneof the copies fail? 
I am thinking of using Begin Tranasaction and Commit Transaction, at what point if it fails can i call RollBack Transaction? 
 
thanks allot 
 
 
Cheers
</div>
</div>

Hi,
What is the best way to hanlde error handling in a sp?

I have a 11 Insert Statements that follow one after the other, how do i reverse my hole process if oneof the copies fail?
I am thinking of using Begin Tranasaction and Commit Transaction, at what point if it fails can i call RollBack Transaction?

thanks allot


Cheers

error hsndling in SQL stored |Proc

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.

Microsoft SQL Server 2008

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.