Why does my stored procedure return -1?

  • Hello,

    I have a stored procedure. The call to it returns -1. What does this mean?

    Here is the code that calls the stored procedure (auto-generated by Entity Framework):


       public virtual int DeleteProjectData(Nullable<int> projectId, string deleteType, string username)
       {
        var projectIdParameter = projectId.HasValue ?
          new ObjectParameter("projectId", projectId) :
          new ObjectParameter("projectId", typeof(int));
     
        var deleteTypeParameter = deleteType != null ?
          new ObjectParameter("deleteType", deleteType) :
          new ObjectParameter("deleteType", typeof(string));
     
        var usernameParameter = username != null ?
          new ObjectParameter("username", username) :
          new ObjectParameter("username", typeof(string));

        int result = ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("DeleteProjectData", projectIdParameter, deleteTypeParameter, usernameParameter);
          return result; // result is -1
       }

    Here is the stored procedure:

    ALTER PROCEDURE [dbo].[DeleteProjectData]
      @projectId INT,
      @deleteType VARCHAR(10),
        @username NVARCHAR(255)
    AS

      SET NOCOUNT ON

      BEGIN TRY

      DECLARE @realProjectId  INT = NULL,
        @isTemplate   BIT,
        @ErrorMessage   NVARCHAR(4000),
        @deleteTypeAll  VARCHAR(10),
        @deleteTypeNodes  VARCHAR(10)

        DECLARE @usernameBinary VARBINARY(255)
        SET @usernameBinary = CAST(@username AS VARBINARY(255))
        SET CONTEXT_INFO @usernameBinary

      SELECT @deleteTypeAll = 'All',
        @deleteTypeNodes = 'Nodes'
       
      -- ensure the input project exists and is not a template
      SELECT @realProjectId = ProjectId
      FROM Project
      WHERE ProjectId = @projectId

      IF @deleteType NOT IN (@deleteTypeAll, @deleteTypeNodes)
       BEGIN
       -- create a the error message for a project that does not exist    
       SELECT @ErrorMessage = N'The input Delete Type is not valid. Valid values are: ''' + @deleteTypeAll
               + ''' and ''' + @deleteTypeNodes + ''''
       
       -- raise the error
       RAISERROR (@ErrorMessage, 16, 1)

       END

      IF @realProjectId IS NULL
       BEGIN
       -- create a the error message for a project that does not exist    
       SELECT @ErrorMessage = N'The input ProjectId does not have a corresponding Project record. No calculations will be performed. ProjectId: ' + CAST(@projectId AS VARCHAR)
       
       -- raise the error
       RAISERROR (@ErrorMessage, 16, 1)

       END

      BEGIN TRANSACTION

        -- If deleting all, delete all reports associated with project first:
        IF @deleteType = @deleteTypeAll
            BEGIN

            DELETE [dbo].[ReportComment]
            WHERE ReportId in (SELECT ReportId
            FROM [dbo].[Report]
            WHERE ProjectId = @realProjectId)

            DELETE [dbo].[ReportMetric]
            WHERE ReportId in (SELECT ReportId
            FROM [dbo].[Report]
            WHERE ProjectId = @realProjectId)

            DELETE [dbo].[ReportTopN]
            WHERE ReportId in (SELECT ReportId
            FROM [dbo].[Report]
            WHERE ProjectId = @realProjectId)

            DELETE [dbo].[Report]
            WHERE ReportId in (SELECT ReportId
            FROM [dbo].[Report]
            WHERE ProjectId = @realProjectId)

            END
        ELSE
            BEGIN

            DELETE [dbo].[ReportTopN]
            WHERE ReportId in (SELECT ReportId
            FROM [dbo].[Report]
            WHERE ProjectId = @realProjectId)

            END
        
      IF @deleteType IN (@deleteTypeAll, @deleteTypeNodes)
       BEGIN
       DECLARE @Keys TABLE (
        ProjectId INT,
        NodeId INT,
        DeviationId INT,
        CauseId INT,
        ConsequenceId INT,
        SafeguardId INT,
        RecommendationId INT,
        RemarkId INT,
        DrawingId INT,
                RiskDataId INT,
                BowtieLoopId INT,
                BowtieId INT)

       -- build a list of keys associated with the project (simplifies delete queries)
       INSERT INTO @Keys (ProjectId,
             NodeId,
             DeviationId,
             CauseId,
             ConsequenceId,
             SafeguardId,
             RecommendationId,
             RemarkId,
             DrawingId,
                             RiskDataId,
                             BowtieLoopId,
                             BowtieId)
        SELECT p.ProjectId,
           n.NodeId,
           d.DeviationId,
           ca.CauseId,                 
           co.ConsequenceId,
           s.SafeguardId,
           r.RecommendationId,
           re.RemarkId,
           dr.DrawingId,
                     rd.RiskDataId,
                     bl.BowtieLoopId,
                     b.BowtieId
        FROM Project p
          LEFT OUTER JOIN Node n
           ON p.ProjectId = n.ProjectId
          LEFT OUTER JOIN Deviation d
           ON n.NodeId = d.NodeId
          LEFT OUTER JOIN Cause ca
           ON d.DeviationId = ca.DeviationId
          LEFT OUTER JOIN Consequence co
           ON ca.CauseId = co.CauseId
          LEFT OUTER JOIN Safeguard s
           ON co.ConsequenceId = s.ConsequenceId
          LEFT OUTER JOIN Recommendation r
           ON co.ConsequenceId = r.ConsequenceId
          LEFT OUTER JOIN Remark re
           ON co.ConsequenceId = re.ConsequenceId
          LEFT OUTER JOIN Drawing dr
           ON r.RecommendationId = dr.RecommendationId
                     LEFT OUTER JOIN Bowtie b
                        ON b.ProjectId = p.ProjectId
                     LEFT OUTER JOIN BowtieLoop bl
                        ON bl.BowtieId = b.BowtieId
                     LEFT Outer JOIN RiskData rd
                        ON rd.BowtieLoopId = bl.BowtieLoopId

        WHERE p.ProjectId = @realProjectId
        
       -- delete the data that was imported

       DELETE FROM Drawing
       WHERE DrawingId IN (SELECT DISTINCT DrawingId
              FROM @Keys)

       DELETE FROM Recommendation
       WHERE RecommendationId IN (SELECT DISTINCT RecommendationId
                FROM @Keys)
      
       DELETE FROM Safeguard
       WHERE SafeguardId IN (SELECT DISTINCT SafeguardId
              FROM @Keys)
              
       DELETE FROM Remark
       WHERE RemarkId IN (SELECT DISTINCT RemarkId
             FROM @Keys)
              
       DELETE FROM Consequence
       WHERE ConsequenceId IN (SELECT DISTINCT ConsequenceId
               FROM @Keys)

       DELETE FROM CauseToBowtieLoopDetails
            Where CauseId IN (Select Distinct CauseId
                          FROM @Keys)
                   
       DELETE FROM Cause
       WHERE CauseId IN (SELECT DISTINCT CauseId
             FROM @Keys)        
            
       DELETE FROM Deviation
       WHERE DeviationId IN (SELECT DISTINCT DeviationId
              FROM @Keys)
      
       DELETE FROM Node
       WHERE NodeId IN (SELECT DISTINCT NodeId
             FROM @Keys)
      
            DELETE FROM RiskData
       WHERE RiskDataId IN (SELECT DISTINCT RiskDataId
             FROM @Keys)    
            
            DELETE FROM BowtieLoop
       WHERE BowtieLoopId IN (SELECT DISTINCT BowtieLoopId
             FROM @Keys)

            DELETE FROM BowtieToEquipmentLookup
            WHERE BowtieId IN (SELECT DISTINCT BowtieId
                                FROM @Keys)
                            
            DELETE FROM Bowtie
       WHERE BowtieId IN (SELECT DISTINCT BowtieId
             FROM @Keys)    

       DELETE FROM ProjectDeviation
       WHERE ProjectId = @realProjectId
      
       DELETE FROM ProjectSafeguard
       WHERE ProjectId = @realProjectId
      
       DELETE FROM ProjectRecommendation
       WHERE ProjectId = @realProjectId
      
       -- also delete from the metrics tables
      
       DELETE FROM ProjectMetrics
       WHERE ProjectId = @realProjectId
      
       DELETE FROM CauseMetrics
       WHERE ProjectId = @realProjectId

       DELETE FROM ConsequenceMetrics
       WHERE ProjectId = @realProjectId
      
       DELETE FROM RecommendationMetrics
       WHERE ProjectId = @realProjectId
      
       DELETE FROM SafeguardMetrics
       WHERE ProjectId = @realProjectId  
                
       END
      
      IF @deleteType = @deleteTypeAll
       BEGIN
      
       -- delete the project specific data (i.e., data not imported)
      
       DELETE FROM RiskMatrixAxis
       WHERE ProjectId = @realProjectId

       DELETE FROM SafeRecCategory
       WHERE ProjectId = @realProjectId

       DELETE FROM Participant
       WHERE ProjectId = @realProjectId

       DELETE FROM RiskRanking
       WHERE ProjectId = @realProjectId
      
       DELETE FROM Category
       WHERE ProjectId = @realProjectId
      
       DELETE FROM ImportFile
       WHERE ProjectId = @realProjectId
      
       DELETE FROM Project
       WHERE ProjectId = @realProjectId
      
       END

      COMMIT TRANSACTION

      END TRY

      BEGIN CATCH

       IF(@@TRANCOUNT > 0)
        BEGIN
        -- rollback all changes if any error occurred
        ROLLBACK TRANSACTION
        END
        
        -- raise the original error
       EXEC RethrowError;
      END CATCH

    Can anyone see why the stored procedure is returning -1? And am I right in interpreting -1 as an error?

  • What does the procedure RethrowError do?

  • Negative return value means error happened during the execution.
    Unless you explicitly change the meaning of @ReturnValue in the proc code.
    Like 
    IF @status = 'Feeling Kinda Silly'
    RETURN -1

    _____________
    Code for TallyGenerator

  • I'll also add that all those IN (SELECT DISTINCTs are a waste of clock cycles because IN will always stop at the first occurrence of whatever it is you're looking for except NULL.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Tuesday, March 6, 2018 8:03 PM

    I'll also add that all those IN (SELECT DISTINCTs are a waste of clock cycles because IN will always stop at the first occurrence of whatever it is you're looking for except NULL.

    The optimiser's smarter than that, it knows they're not needed.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks everyone,

    Turns out this was causing the return of -1:
    SET NOCOUNT ON
    If I set NOCOUNT to OFF, it returns the number of rows.

  • junk.mail291276 - Wednesday, March 7, 2018 7:42 AM

    Thanks everyone,

    Turns out this was causing the return of -1:
    SET NOCOUNT ON
    If I set NOCOUNT to OFF, it returns the number of rows.

    Possibly a bad mistake especially if your app is relying on the single return of error number.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Not only that, you still didn't answer my question.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply