Getting stored procedure's return value with iBatis.NET

Question

How can I retrieve the return value of a stored procedure using iBatis.NET? The below code successfully calls the stored procedure, but the QueryForObject<int> call returns 0.

SqlMap

<procedure id="MyProc" parameterMap="MyProcParameters" resultClass="int">
    MyProc
</procedure>

<parameterMap id="MyProcParameters">
    <parameter property="num"/>
</parameterMap>

C# code

public int RunMyProc( string num )
{
    return QueryForObject < int > ( "MyProc", new Hashtable { { "num", num } } );
}

Stored Procedure

create procedure MyProc
    @num nvarchar(512)
as
begin
    return convert(int, @num)
end

FYI, I'm using iBatis 1.6.1.0, .NET 3.5, and SQL Server 2008.

score 1 · Accepted Answer · answered Mar 17 '10 at 14:13

It's not pretty, but this works:

SqlMap

<statement id="MyProc" parameterClass="string" resultClass="int">
    declare @num int
    exec @num = MyProc #value#
    select @num
</statement>

C# code

public int RunMyProc( string num )
{
    return QueryForObject < int > ( "MyProc", num );
}

score 1 · Answer 2 · answered Apr 05 '11 at 09:51

You might want to check out the following article http://www.barebonescoder.com/2010/04/ibatis-net-stored-procedures-return-values/ on how to retrieve return values.

I've used it in QueryForObject and Insert scenarios where the last statement is a return statement in the stored procedure.

Pay particular attention to the class attribute on the "parameterMap" element. It's a lot prettier than the answer above and I believe it's more inline with the way IBatis.Net was intended to be used.

score 0 · Answer 3 · answered Mar 17 '10 at 14:07

0

Stored procedures don't have a return value like functions.
So, I don't think that will work. Try using output parameters instead.

answered Mar 17 '10 at 14:07

shahkalpesh

33,172
3
63
88

Stored procedures can return values in SQL Server. – MikeWyatt Mar 17 '10 at 14:17
Yes, you are right. I am not sure, if one should use that feature (assuming that callers won't look at return value, if it is a stored procedure). – shahkalpesh Mar 17 '10 at 14:40
whenever you write code called by someone else, you can't MAKE them use it properly. There is no reason to not use all the features available. I feel it is better to use the return value as an indication of an error, 0=ok, <0 is minor error (validation, etc.), >0 is fatal error (constraint, etc) For the code in this question, an output parameter is best, so an error code could be returned when the string is not a number. Return values have to be a non null integer. Output parameters can be just about any data type. – KM. Mar 17 '10 at 17:43

score 0 · Answer 4 · answered Mar 17 '10 at 14:13

I'm not sure about your application logic, but your procedure would be better like this:

create procedure MyProc
    @num nvarchar(512)
as
begin
    DECLARE @ReturnValue int
    BEGIN TRY
        SET @ReturnValue=convert(int, @num)
    END TRY
    BEGIN CATCH
        SET @ReturnValue=0 --procedures can not return null, so set some default here
    END CATCH
    return @ReturnValue
end

Getting stored procedure's return value with iBatis.NET

4 Answers4