July 4, 2006 at 1:46 pm
Why would the index tuning wizard (or anyone write):
IF( @@error <> 0 )
SET @bErrors = 1
IF( @bErrors = 0 )
rather than just:
IF( @@error = 0 )
Full code:
DECLARE @bErrors as bit
BEGIN TRANSACTION
SET @bErrors = 0
CREATE NONCLUSTERED INDEX [client_payments25] ON [dbo].[client_payments] ([cp_client_id] ASC, [cp_order_id] ASC, [cp_date] ASC, [cp_amount] ASC, [cp_biller_name] ASC )
IF( @@error <> 0 ) SET @bErrors = 1
IF( @bErrors = 0 )
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
July 4, 2006 at 5:31 pm
Becuase the code is generic.
bErrors is a flag which is set to 1 if any error occurs, then referred to at the end to determine whether to commit or roll back.
This code has only one statement in the transaction, so the construct is strictly redundant.
It could be there in compliance to formal or informal, personal or shared, coding standards.
It could also be generated (as in this case) by a sql-generation program like the index tuning wizard, since there is no point writing code specifically to remove the harmless generic code from a 1-statement transaction, which is just a limit case of an (n:n>0)-statement transaction.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 5, 2006 at 11:43 am
Perhaps this is obvious to most, but I noted Gino is a newbie and might not appreciate that @@error is very transient, in that its value does not persist beyond the next statement executed.
It is a standard practice to persist "@@" variables into corresponding user-defined values that can be tested at points other than immediately following the statement whose errors are of interest.
July 5, 2006 at 3:49 pm
@@rowcount is another example. If you want to capture both @@rowcount and @@error, you have to get both at the same time, directly after the statement you are 'watching', otherwise the process of capturing or using one resets the other.
select
@error = @@error, @rowcount = @@rowcount
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply