2

I have a SQL Server stored procedure with three conditions. Currently, only the first IF block will execute no matter the parameters:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

/*
DESCRIPTION: Assigns/reassigns orders in [GIS].[dbo].[QCOrders]
PARAMETERS:
    @FldrInitKey    VARCHAR(30) = NULL,
    @UserName       VARCHAR(20) = NULL,
    @QCTrack        DECIMAL(6,2),
    @WF             BIT,
    @idQC           INT         = NULL
RETURNS:
    @RC 0 (success), 1 (failure)
EXAMPLE:
    EXEC spQCAssignOrders '889G1Pt', 'arajendran', '2016.10', '0', NULL
MODIFICATIONS:
    03/10/2016  A.Rajendran Created
*/

ALTER PROCEDURE [dbo].[spQCAssignOrders]
    @FldrInitKey VARCHAR(30) = NULL,
    @UserName    VARCHAR(20) = NULL,
    @QCTrack     DECIMAL(6,2),
    @WF          BIT,
    @idQC        INT = NULL
AS
    SET NOCOUNT ON
    SET ROWCOUNT 0
    SET XACT_ABORT ON

BEGIN TRY
    /* Declare and initialize variables */
    DECLARE @RC INT, @TranCount INT

    SELECT 
        @RC = 0, @TranCount = @@TRANCOUNT

    IF @TranCount = 0 
    BEGIN TRANSACTION
        IF @idQC IS NOT NULL
        BEGIN
            UPDATE [GIS].[dbo].[QCOrders]
            SET UserName = @UserName,
                AssignedDte = CASE  
                                 WHEN @UserName IS NULL 
                                    THEN NULL
                                    ELSE GETDATE()
                              END,
                Unable = NULL,
                Error = NULL,
                CompletedDate = NULL,
                AdminCheck = NULL
            WHERE 
                idQC = @idQC
        END

        IF @FldrInitKey IS NOT NULL
        BEGIN
            UPDATE [GIS].[dbo].[QCOrders]
            SET UserName = @UserName,
                AssignedDte = GETDATE()
            WHERE   
                QCTrack = @QCTrack
                AND FldrInitKey = @FldrInitKey
                AND WellsFargo = @WF
                AND UserName IS NULL
                AND Unable <> 1
        END

        IF @FldrInitKey IS NULL
        BEGIN
            UPDATE [GIS].[dbo].[QCOrders]
            SET UserName = @UserName,
                AssignedDte = GETDATE()
            WHERE   
                QCTrack = @QCTrack
                AND WellsFargo = @WF
                AND UserName IS NULL
                AND Unable <> 1
        END

        IF @TranCount = 0 AND (XACT_STATE()) = 1 
           COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF (XACT_STATE()) = -1 
            ROLLBACK TRANSACTION

        EXEC [TOD].[dbo].[spRethrowError]
        SET @RC = 1
    END CATCH

    SET NOCOUNT OFF
    SET ROWCOUNT 0

    RETURN @RC


GRANT EXECUTE ON [GIS].[dbo].[spQCAssignOrders] TO db_allowexec

If I want to execute this:

EXEC spQCAssignOrders '889G1Pt', 'arajendran', '2016.10', '0', NULL

the second IF statement should execute, but it doesn't. Why?

Clarification

Second IF statement:

IF @FldrInitKey IS NOT NULL
BEGIN
    UPDATE [GIS].[dbo].[QCOrders] 
    SET UserName = @UserName,
        AssignedDte = GETDATE()
    WHERE  
        QCTrack = @QCTrack
        AND FldrInitKey = @FldrInitKey
        AND WellsFargo = @WF
        AND UserName IS NULL
        AND Unable <> 1
END
9
  • 1
    can you specify which IF?. For me, the second IF is IF @idQC IS NOT NULL, and @idQC is NULL Commented Aug 8, 2016 at 19:10
  • The second IF should be IF @FldrInitKey IS NOT NULL Commented Aug 8, 2016 at 19:11
  • 1
    ok, but...well, your first IF is IF @TranCount = 0. You should clarify that in your question instead of a comment Commented Aug 8, 2016 at 19:13
  • Put some print statements in your IF statements and see if they get printed. Make sure the transaction is getting committed. Commented Aug 8, 2016 at 19:13
  • 1
    what do you mean?, it is basic T-SQL, and it still is the second IF Commented Aug 8, 2016 at 19:14

1 Answer 1

1

You could greatly simplify this. All three update statements can be turned into a single update statement. This should be the equivalent of all three.

UPDATE [GIS].[dbo].[QCOrders]
                SET 
                    UserName    = @UserName,
                    AssignedDte = 
                    CASE    
                        WHEN @idQC IS NOT NULL and @UserName IS NULL THEN NULL
                        ELSE GETDATE()
                    END

            WHERE   QCTrack         = @QCTrack
                    AND FldrInitKey = ISNULL(@FldrInitKey, FldrInitKey)
                    AND WellsFargo  = @WF
                    AND UserName IS NULL
                    AND Unable <> 1
Sign up to request clarification or add additional context in comments.

2 Comments

AssignedDte x2 confuses me. Also, FldrInitKey is never NULL in the table, it has a range of values. So, the ISNULL statement would not work. Sometimes, I need to execute regardless of the FldrInitKey value, though.
Looks closer at that ISNULL. It is NOT looking at the column, it is looking at your variable. The AssignedDte part is just a typo from me. I will fix it. :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.