In debugging a related problem I discovered the following scenario where a RAISERROR executed in an MS SQL 2005 stored proc did not result in an exception in my calling code. Whether the exception is raised or not depends on whether the stored proc returns a result set. In the stored proc code below an exception is never raised in the calling code. If the line 'SELECT @TestValue AS TestValue' is commented out then an exception occurs in the calling code.

The minimum stored proc code, htm.xml, and calling code to reproduce the problem with nhibernate version 1.2.0.4 looks like

dbo.procRaiseErrorTest
===============
DECLARE @TestValue INT
DECLARE @LocalError INT
DECLARE @ErrorMessage VARCHAR( 1024 )

SELECT @TestValue = 1

SELECT @TestValue AS TestValue

SELECT @LocalError = -1

IF( 0 <> @LocalError )
BEGIN
SELECT @ErrorMessage = 'Error'
RAISERROR( @ErrorMessage, 16, 1 )
END

hbm.xml
======
<sql-query name="RaiseErrorTest">
<return-scalar column="TestValue" type="Int32"/>
EXEC dbo.procRaiseErrorTest
</sql-query>


Calling code
========
public class RaiseErrorTest
{
private int m_TestValue;
public int TestValue
{
get
{
return m_TestValue;
}
set
{
m_TestValue = value;
}
}
}

try
{
ISession Session = NHibernateHelper.GetCurrentSession( );
ITransaction TX = Session.BeginTransaction( );

IQuery Query = Session.GetNamedQuery( "RaiseErrorTest" )
.SetResultTransformer( new NHibernate.Transform.AliasToBeanResultTransformer( typeof ( RaiseErrorTest ) ) );
List<RaiseErrorTest> list = ( List<RaiseErrorTest> ) Query.List<RaiseErrorTest>( );
TX.Commit( );
Session.Close( );
}
catch ( Exception _Exception )
{
throw _Exception;
}