CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
MovingRoot Lee
MovingRoot Lee

Reputation: 65

Stored procedure always returns 0, even if the process inside works fine

I'm writing a stored procedure for deleting data. It should delete a program only if no field is connected to that program.

My logic is to count columns related to TA_Program by Id first, and if count returns 0 then delete the program, if returns -3 then alert for field connection, and else for alert for internet connection.

However, even if the deleting process itself works well, the stored procedure always returns 0 so that alert for failure I made always come up.

I read several posts about @@ROWCOUNT, relationship between delete and ROWCOUNT, but every try returned 0, nothing else. Would you please give me help to solve this problem?

This is my stored procedure and how it is queried.

CREATE PROCEDURE SP_Delete_Program_By_Id 
    (@Id int)
AS
BEGIN
    DECLARE @FieldCount AS int
    DECLARE @ResultCount AS int
    
    -- count FieldId Where Id = @Id
    SELECT @FieldCount = (SELECT COUNT(*) FROM TA_Field (Nolock) WHERE Id = @Id)
       
    -- if no Field is connected, delete program
    IF @FieldCount = 0
    BEGIN
        DELETE FROM TA_Program
        WHERE Id = @Id

        SELECT @FieldCount = (SELECT COUNT(*) FROM TA_Program (Nolock) WHERE Id = @Id)

        IF @FieldCount = 0
            SELECT @ResultCount = 1
        ELSE
            SELECT @ResultCount = -1
    END
    ELSE
        SELECT @ResultCount = -3

    RETURN @ResultCount
END

C# code:

    public static int DeleteProgram(Program obj)
    {
        int result = -1;

        // Sort is for determining db id and pw stored 
        using (var db = DBConnectionHelper.GetConnection(Sort.Program))
        {
            var param = new DynamicParameters();
            //param.Add("@FieldId", obj.FieldId);
            param.Add("@Id", obj.Id, dbType: DbType.Int32);
            result = db.Query<int>("APISP_Delete_Program_By_Id", param: param, commandType: CommandType.StoredProcedure).SingleOrDefault<int>();
        }

        return result;
        // this always returns 0
    }

    [HttpPost]
    public APIResult DeleteProgram(string session, Program program)
    {
        int deleteResult = ProgramsDbHelper.DeleteProgram(program);

        if (deleteResult == 1)
        {
            result.ResultCode = 1;
            return result;
        }

        if (deleteResult == -3)
        {
            result.ResultCode = -3;
            return result;
        }            

        return result;
    }

Upvotes: 0

Views: 1190

Answers (1)

Charlieface
Charlieface

Reputation: 72040

Your procedure has a number of issues

  • Your primary issue: using RETURN instead of SELECT
  • Use of NOLOCK should be avoided
  • Use of sp_ prefix should be avoided
  • Assuming TA_Field is foreign-keyed to TA_Program, the logic doesn't make sense: because you have no transaction and are not using HOLDLOCK, UPDLOCK, it is possible for another user to insert into TA_Program in between your SELECT COUNT and DELETE, at which point you will get an error, and the rest of the procedure will not run. If no-one inserted then the extra SELECT COUNT is useless, because the data hasn't changed.
  • You don't actually need any of that logic, because you can combine it in the DELETE statement (every statement is in its own transaction anyway).

So your procedure should look like this instead:

CREATE PROCEDURE Delete_Program_By_Id 
    (@Id int)
AS

SET NOCOUNT ON;

DELETE FROM TA_Program
WHERE Id = @Id
  AND NOT EXISTS (SELECT 1
      FROM TA_Field
      WHERE Id = @Id);
  
SELECT CASE WHEN @@ROWCOUNT > 0 THEN 1 ELSE -3 END;

GO

Upvotes: 2

Related Questions