Prev: 2008R2 Views with ORDER BY clause
Next: Order By String Problem
From: Jeroen Mostert on 22 May 2010 12:51
On 2010-05-22 17:39, Paarthy wrote:
> Msg 0, Level 11, State 0, Line 0
> A severe error occurred on the current command. The results, if any,
> should be discarded.
>
This indicates a bug in SQL Server; your session has crashed.

> when we use a return statement in the stored procedure.
>
> Below the stored procedure:
>
>
> create procedure sp_test

You shouldn't have your stored procedure names start with sp_ because this
causes potential clashes with stored procedures defined in the master
database. See http://msdn.microsoft.com/library/aa214379 for more information.

> (
> @a as int
> )
> as
> begin
> begin try
> begin transaction
> declare @b as int
> set @b = 1
> if @a = @b
> begin
> select 'i am rolling back'
> rollback tran
> return 1
> end
> end try
> begin catch
> if @@trancount>1
> begin
> rollback tran
> end
> end catch
> end
>
> ----------------------------------------------------------------------------------------------------------
>
> when the stored procedure is executed in the below way,
> exec sp_test 1
>
> -----------------------------------------------------------------------------------------------------------
> But we get error, when we execute in the following way...
> can any one please help?
>
> begin tran
> exec sp_test 1
> rollback tran
>
This particular bug is fixed in SQL Server 2008, see
http://connect.microsoft.com/SQLServer/feedback/details/382985/. There is no
fix for SQL Server 2005.

You may be able to work around the issue by removing the RETURN and having
the client check for the message you get instead:

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

Of course, if you need this to work from within another stored procedure
it's no help. You can use an output variable to pass the status instead of
the return value, that doesn't trigger the bug (but requires an interface
change).

--
J.
 | 
Pages: 1
Prev: 2008R2 Views with ORDER BY clause
Next: Order By String Problem