sql server programming: Can someone please explain why I get the following error
when running the following code.

Error message 'Server: Msg 266, Level 16, State 2,
Procedure sp_test, Line 3
Transaction count after EXECUTE indicates that a COMMIT or
ROLLBACK TRANSACTION statement is missing. Previous count
= 1, current count = 0.'

CODE
create procedure sp_test
as
rollback tran
go

begin tran
execute sp_test
go

drop procedure sp_test
go

The answer is in the Books Online under Error 266. This is expected
behavior, and they provide ways to avoid it if you wish.

Mike Kruchten


[quoted text, click to view]

I guess there is a "rollback" but it does not found the COMMIT, so it is a
not match issue
I copy this document from Book Online and it explain how rollback will be
used:

Rolls back an explicit or implicit transaction to the beginning of the
transaction, or to a savepoint inside a transaction.

Syntax
ROLLBACK [ TRAN [ SACTION ]
[ transaction_name | @tran_name_variable
| savepoint_name | @savepoint_variable ] ]

Arguments
transaction_name

Is the name assigned to the transaction on BEGIN TRANSACTION.
transaction_name must conform to the rules for identifiers, but only the
first 32 characters of the transaction name are used. When nesting
transactions, transaction_name must be the name from the outermost BEGIN
TRANSACTION statement.

@tran_name_variable

Is the name of a user-defined variable containing a valid transaction name.
The variable must be declared with a char, varchar, nchar, or nvarchar data
type.

savepoint_name

Is savepoint_name from a SAVE TRANSACTION statement. savepoint_name must
conform to the rules for identifiers. Use savepoint_name when a conditional
rollback should affect only part of the transaction.

@savepoint_variable

Is name of a user-defined variable containing a valid savepoint name. The
variable must be declared with a char, varchar, nchar, or nvarchar data
type.

Remarks
ROLLBACK TRANSACTION erases all data modifications made since the start of
the transaction or to a savepoint. It also frees resources held by the
transaction.

ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back
to the beginning of the transaction. When nesting transactions, this same
statement rolls back all inner transactions to the outermost BEGIN
TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the
@@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does
not decrement @@TRANCOUNT.

A ROLLBACK TRANSACTION statement specifying a savepoint_name does not free
any locks.

ROLLBACK TRANSACTION cannot reference a savepoint_name in distributed
transactions started either explicitly with BEGIN DISTRIBUTED TRANSACTION or
escalated from a local transaction.

A transaction cannot be rolled back after a COMMIT TRANSACTION statement is
executed.

Within a transaction, duplicate savepoint names are allowed, but a ROLLBACK
TRANSACTION using the duplicate savepoint name rolls back only to the most
recent SAVE TRANSACTION using that savepoint name.

In stored procedures, ROLLBACK TRANSACTION statements without a
savepoint_name or transaction_name roll back all statements to the outermost
BEGIN TRANSACTION. A ROLLBACK TRANSACTION statement in a stored procedure
that causes @@TRANCOUNT to have a different value when the trigger completes
than the @@TRANCOUNT value when the stored procedure was called produces an
informational message. This message does not affect subsequent processing.

If a ROLLBACK TRANSACTION is issued in a trigger:

a.. All data modifications made to that point in the current transaction
are rolled back, including any made by the trigger.


b.. The trigger continues executing any remaining statements after the
ROLLBACK statement. If any of these statements modify data, the
modifications are not rolled back. No nested triggers are fired by the
execution of these remaining statements.


c.. The statements in the batch after the statement that fired the trigger
are not executed.
@@TRANCOUNT is incremented by one when entering a trigger, even when in
autocommit mode. (The system treats a trigger as an implied nested
transaction.)

ROLLBACK TRANSACTION statements in stored procedures do not affect
subsequent statements in the batch that called the procedure; subsequent
statements in the batch are executed. ROLLBACK TRANSACTION statements in
triggers terminate the batch containing the statement that fired the
trigger; subsequent statements in the batch are not executed.

A ROLLBACK TRANSACTION statement does not produce any messages to the user.
If warnings are needed in stored procedures or triggers, use the RAISERROR
or PRINT statements. RAISERROR is the preferred statement for indicating
errors.

The effect of a ROLLBACK on cursors is defined by these three rules:

1.. With CURSOR_CLOSE_ON_COMMIT set ON, ROLLBACK closes but does not
deallocate all open cursors.


2.. With CURSOR_CLOSE_ON_COMMIT set OFF, ROLLBACK does not affect any open
synchronous STATIC or INSENSITIVE cursors or asynchronous STATIC cursors
that have been fully populated. Open cursors of any other type are closed
but not deallocated.


3.. An error that terminates a batch and generates an internal rollback
deallocates all cursors that were declared in the batch containing the error
statement. All cursors are deallocated regardless of their type or the
setting of CURSOR_CLOSE_ON_COMMIT. This includes cursors declared in stored
procedures called by the error batch. Cursors declared in a batch before the
error batch are subject to rules 1 and 2. A deadlock error is an example of
this type of error. A ROLLBACK statement issued in a trigger also
automatically generates this type of error.
Permissions
ROLLBACK TRANSACTION permissions default to any valid user.





[quoted text, click to view]