0

I call SQL procedure (SQL Server 2008) from a C# service:

var retval = sqlCommand.Parameters.Add("@ret", SqlDbType.Int);
retval.Direction = ParameterDirection.ReturnValue;

SqlDataReader reader = null;
try
{
    try
    {
        reader = sqlCommand.ExecuteReader();
    }
    catch (Exception ex)
    {
        if (retval.Value != null && retval.Value != DBNull.Value && (int)retval.Value != 0)
        {
            var errorCode = (int)retval.Value;
            throw new ApiForwardingException(ex.Message, errorCode); //прокидываем пользователю
        }
        else throw; //не прокинется пользователю
    }

    //исключения не было, но код != 0
    if (retval.Value != null && (int)retval.Value != 0)
    {
        var errorCode = (int)retval.Value;
        if (getErrorCodeMessage) // получаем сообщения по коду и прокидываем его пользователю
        {
            var codes = GetErrorCodes();
            if (codes.ContainsKey(errorCode) == false)
                throw new Exception(
                    string.Format("Процедура {0} завершилась с кодом возврата {1}. Cообщение, соответствующее коду найти не удалось.",
                    sqlCommand.CommandText, errorCode)
                );
            else throw new ApiForwardingException(codes[errorCode], errorCode);
        }
        else
        {
            throw new Exception(string.Format("Процедура {0} завершилась с кодом возврата {1}.",
                sqlCommand.CommandText, (int)retval.Value));
        }

In most cases it works fine except one procedure. It returns error code inside try-catch block and my service gets null return value.

    begin try 
        -- сохраняем задание
        insert into [IPAddressTask](
            , IPAddressTaskTypeId
            , TaskCreationDate   
            , TaskStateID      
        )
        select @TaskTypeId       as IPAddressTaskTypeId
            , @TaskCreationDate as TaskCreationDate
            , @ScheduledStateId as TaskStateID

        set @TaskId = scope_identity()
        
    end try
    begin catch      
        return -1000
    end catch

If a call this procedure directly from SSMS and get its return value, it; all ok

declare @ret int
exec @ret = rsi.p_CreateIPAddressDeleteTask 1, 1, null, null, null
select @ret

--- 
-1000

I've changed return block in procedure from try-catch to an old-style

if (@@error <> 0) return -1000

It works fine, I get return value and can handle it. But I want to have universal solution, which can handle with any variant of setting return value in SQL

Update: forgot C# code handling not-zero ret-value

Dale K
  • 25,246
  • 15
  • 42
  • 71
Shtasi
  • 1
  • 1
  • So the C# code catches an exception when sp is returning `-1000` ? And you are not able to get `-1000` in `retval.Value`? – Chetan Oct 28 '22 at 05:48
  • 1
    Your C# code retrieves the error *only* if an exception occurs, which isn't happening because the `BEGIN TRY` is ensuring no error bubbles up to the caller. You would need to move that code into the `try`. – Jeroen Mostert Oct 28 '22 at 05:49
  • updated code inside question. added not-zero ret-value handler – Shtasi Oct 28 '22 at 05:54
  • Does [this](https://stackoverflow.com/questions/22231881/get-return-value-from-from-sql-using-execute-reader) answer your question? The Return Value is not populated until you have completed reading the rows from the recordset. – Dale K Oct 28 '22 at 06:04
  • @Dale K may be that's the problem. but then a can't understand why deleting try-catch changes the behavior of the code/ – Shtasi Oct 28 '22 at 06:10

1 Answers1

0

If possible change the catch block in the SP. Use raiseerror (or throw in SQL Server 2012++) in the catch block. If an error is caught in SQL your C# code will also catch the error.

BEGIN CATCH    
    RAISERROR ('Error raised in TRY block.', -- Message text.
        16, -- Severity.
        1 -- State.
    );    
END CATCH
Dale K
  • 25,246
  • 15
  • 42
  • 71
Mihal By
  • 162
  • 2
  • 12