CODEWITHSUNDEEP
c#asp.netsqltransactions
TheGateKeeper
TheGateKeeper

Reputation: 4530

ExecuteScalar returning null

I have an sql query that uses transactions, and it uses return to indicate failure or success, like so:

BEGIN

    DECLARE @intErrorCode INT;
    DECLARE @emailAutoIncrement INT;

    BEGIN TRAN


    INSERT  INTO Accounts ( blabla, blabla2 )
    VALUES  ( somevalue, somevalue2 )

    Select @intErrorCode = @@ERROR
    if (@intErrorCode <> 0) GOTO PROBLEM

    COMMIT TRAN
    RETURN 1


    PROBLEM:
    if (@intErrorCode <> 0) Begin
    Rollback Tran
    RETURN 0
    END

END

From code, I used sqlCommand.ExecuteScalar() thinking that this will give me either 1 or 0, but this always returns false. Also, another problem I have is that when an exception happens in the sql and the program is meant to return 0, it actually returns the exception.

So the RETURN 0 is useless because it is always overridden by the exception?

To add some more information, the sql query is a stored procedure and it is called like so:

                myCommand.CommandText = "createAccount";
                myCommand.CommandType = CommandType.StoredProcedure;
                //Add parameters here
                using (myReader)
                {
                    var test = myCommand.ExecuteScalar();
                }

Upvotes: 1

Views: 2054

Answers (2)

Dave Hogan
Dave Hogan

Reputation: 3221

try SELECT rather than RETURN

BEGIN

    DECLARE @intErrorCode INT;
    DECLARE @emailAutoIncrement INT;

    BEGIN TRAN


    INSERT  INTO Accounts ( blabla, blabla2 )
    VALUES  ( somevalue, somevalue2 )

    SET @intErrorCode = @@ERROR
    if (@intErrorCode <> 0) GOTO PROBLEM

    COMMIT TRAN
    SELECT 1


    PROBLEM:
    if (@intErrorCode <> 0) Begin
    Rollback Tran
    SELECT 0
    END

END

I also changed 

SELECT @intErrorCode = @@ERROR

to

SET @intErrorCode = @@ERROR

Upvotes: 3

mslliviu
mslliviu

Reputation: 1138

Try instead of Return 1 to use Select 1

For the second problem in MSSQL is recommended to use try catch blocks.

This is what I use, don't know if it is best practice, but it allows more possibilities, you can log errors, customize the text of messages

BEGIN TRY...
END TRY
BEGIN CATCH
    declare @ErrorMessage nvarchar(4000),@ErrorSeverity int,@ErrorState int
    select @ErrorMessage = ERROR_MESSAGE(),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState=ERROR_STATE()
    set @ErrorState=case when isnull(@ErrorState,0) <1 then 1 else @ErrorState end  
    raiserror (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH

Upvotes: 1

Related Questions