Executing stored procedure not working but individual statements working in stored procedure

Question

I have the following stored procedure which I am trying to execute.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_Create] 
(
    @OriginatingTransactionID VARCHAR(50),
    @AssociatedOriginatingTransactionID VARCHAR(50),
    @LineOfBusiness VARCHAR(50),
    @RiskState VARCHAR(50),
    @OccupationCode VARCHAR(50),
    @SourceSystem VARCHAR(50),
    @DocumentCategory VARCHAR(50),
    @DocumentType VARCHAR(50),
    @TransactionFlow VARCHAR(50),
    @BundleName VARCHAR(50),
    @DocumentID VARCHAR(50),
    @DocumentName VARCHAR(50),
    @PolicyOrClaimNumber VARCHAR(50),
    @EffectiveOrCreationDate DATETIME,
    @SignatureDetect BIT

)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @FinalDate DATETIME, @RuleID VARCHAR(50),
            @RuleName VARCHAR(50), @CurrentActionID INT,
            @BundleTransactionID INT

    IF (@SignatureDetect = 'true')
    BEGIN
        IF (@OriginatingTransactionID = @AssociatedOriginatingTransactionID)

        BEGIN       
            IF NOT EXISTS (SELECT t.Doc_Checklist_TXN_ID FROM (SELECT  bundlechecklist.Bundle_TXN_ID, documentchecklist.Doc_Checklist_TXN_ID, documentchecklist.Doc_Type_Name FROM ecm.bundle_checklist_txn bundlechecklist
                LEFT OUTER JOIN [ECM].[Document_Checklist_TXN] documentchecklist ON documentchecklist.Bundle_TXN_ID = bundlechecklist.Bundle_TXN_ID
                WHERE bundlechecklist.originating_tran_id = @OriginatingTransactionID AND bundle_name = @BundleName)t WHERE t.Doc_Checklist_TXN_ID is not null )

                BEGIN

                    UPDATE ecm.bundle_checklist_txn 
                    SET Bundle_Status_Code = 'COMP'
                    FROM ecm.bundle_checklist_txn bundlechecklist
                    INNER JOIN (SELECT DISTINCT Bundle_TXN_ID,Document_ID FROM ecm.Document_Checklist_TXN documentchecklist
                    WHERE Doc_Status_Code='COMP') COMP
                    ON COMP.Bundle_TXN_ID = bundlechecklist.Bundle_TXN_ID
                    AND COMP.Bundle_TXN_ID NOT IN (
                    SELECT Bundle_TXN_ID FROM ecm.Document_Checklist_TXN documentchecklist
                    WHERE documentchecklist.Bundle_TXN_ID=COMP.Bundle_TXN_ID AND documentchecklist.Doc_Status_Code<>'COMP') 
                    AND COMP.Document_ID=@DocumentID
                    WHERE bundlechecklist.Bundle_TXN_ID= @OriginatingTransactionID
                    AND bundlechecklist.Assoc_Orig_Tran_ID=@AssociatedOriginatingTransactionID

                    UPDATE associatedtransaction
                    SET Assoc_Orig_Status_Code='COMP'
                    FROM [ECM].[Assoc_Orig_Tran_TXN] associatedtransaction
                    INNER JOIN (SELECT DISTINCT Assoc_Orig_Tran_ID from [ECM].[Bundle_Checklist_TXN] bundlechecklist
                    WHERE bundlechecklist.[Bundle_Status_Code]='COMP')COMP
                    ON COMP.Assoc_Orig_Tran_ID=associatedtransaction.Assoc_Orig_Tran_ID
                    WHERE COMP.Assoc_Orig_Tran_ID=@AssociatedOriginatingTransactionID
                END
        END

    END
ELSE
    BEGIN
        IF (@OriginatingTransactionID = @AssociatedOriginatingTransactionID)
           BEGIN
            SELECT @FinalDate =DATEADD(DAY,Number_Of_Days_Till_Next_Action,@EffectiveOrCreationDate),@RuleID= rule1.Action_Rule_ID,@RuleName=rule1.Action_Rule_Name,@currentActionID=rule1.Current_Action_ID FROM ecm.Action_Rule rule1
                INNER JOIN ecm.Action_Rule_Group rulegroup ON rulegroup.Action_Rule_Group_ID = rule1.Action_Rule_Group_ID
                INNER JOIN ref.Line_of_Business lob ON lob.Line_of_Business_Code = rulegroup.Line_of_Business_Code 
                INNER JOIN ref.State state ON state.State_Alpha_Code=rulegroup.State_Alpha_Code
                INNER JOIN ref.Source_System sourcesystem ON sourcesystem.Source_System_ID = rulegroup.Source_System_ID
                INNER JOIN ecm.[Document Catgeory] documentcategory ON documentcategory.Doc_Categ_ID = rulegroup.Doc_Categ_ID
                INNER JOIN ecm.[Document Type] documenttype ON documenttype.Doc_Type_ID=rule1.Doc_Type_ID
                INNER JOIN ecm.Exec_Acct_Group occupationgroup ON occupationgroup.Exec_Acct_Grp_ID = rulegroup.Exec_Acct_Grp_ID  
                INNER JOIN [ECM].[Bundle] bundle ON bundle.Bundle_ID = rule1.Bundle_ID 

            WHERE 
                bundle.Bundle_Name = @BundleName
                AND rulegroup.Line_of_Business_Code=@LineOfBusiness
                AND rulegroup.State_Alpha_Code=@RiskState
                AND documentcategory.Doc_Categ_Name=@DocumentCategory
                AND rulegroup.Exec_Acct_Grp_ID=@OccupationCode
                AND rule1.Transaction_Flow=@TransactionFlow 
                AND sourcesystem.Source_System_Name = @SourceSystem 
                AND rulegroup.Doc_Categ_ID=documentcategory.Doc_Categ_ID
                AND documenttype.Doc_Type_Name =@DocumentType    

        IF NOT EXISTS (SELECT Assoc_Orig_Tran_ID FROM [ECM].[Assoc_Orig_Tran_TXN] WHERE Assoc_Orig_Tran_ID=@AssociatedOriginatingTransactionID)
            BEGIN
                INSERT INTO  [ECM].[ASsoc_Orig_Tran_TXN] 
                    (Orig_Tran_Action_Rule_id, Assoc_Orig_Tran_ID, Assoc_Orig_Status_Code, First_Rule_Date, Final_Actionable_Date, Folder_ID,CREATED_DTM,CREATE_PROCESSNAME,UPDATE_DTM,UPDATE_PROCESSNAME)
                VALUES 
                    (@RuleID,@AssociatedOriginatingTransactionID, 'PEND', @EffectiveOrCreationDate, @FinalDate,NULL,GETDATE(),CURRENT_USER,GETDATE(),CURRENT_USER)
            END


        IF NOT EXISTS (SELECT originating_tran_id FROM ecm.bundle_checklist_txn WHERE originating_tran_id = @OriginatingTransactionID AND bundle_name = @BundleName)
            BEGIN
                INSERT INTO  [ECM].[Bundle_Checklist_TXN] 
                    ( Bundle_Action_Rule_ID,Bundle_Name,Bundle_Status_Code,ASsoc_Orig_Tran_ID,Originating_Tran_ID,Doc_Categ_Name, Next_Action_Dt,CREATED_DTM,CREATE_PROCESSNAME,UPDATE_DTM,UPDATE_PROCESSNAME)
                VALUES 
                    (@RuleID,@BundleName,'PEND',@AssociatedOriginatingTransactionID,@OriginatingTransactionID,@DocumentCategory,@FinalDate,GETDATE(),CURRENT_USER,GETDATE(),CURRENT_USER)
            END


        IF NOT EXISTS (SELECT t.Doc_Checklist_TXN_ID FROM (SELECT  bundlechecklist.Bundle_TXN_ID, documentchecklist.Doc_Checklist_TXN_ID, documentchecklist.Doc_Type_Name FROM ecm.bundle_checklist_txn bundlechecklist
            LEFT OUTER JOIN [ECM].[Document_Checklist_TXN] documentchecklist ON documentchecklist.Bundle_TXN_ID = bundlechecklist.Bundle_TXN_ID
                WHERE bundlechecklist.originating_tran_id = @OriginatingTransactionID AND bundle_name = @BundleName)t WHERE t.Doc_Checklist_TXN_ID is not null )

            BEGIN
                SELECT @BundleTransactionID=bundlechecklist.bundle_txn_id FROM [ECM].[Bundle_Checklist_TXN] bundlechecklist
                    INSERT INTO [ECM].[Document_Checklist_TXN]
                        (bundle_txn_id, document_action_rule_id, action_rule_name, doc_type_name, cp_document_name, document_id, doc_status_code,CREATED_DTM,CREATE_PROCESSNAME,UPDATE_DTM,UPDATE_PROCESSNAME)
                 VALUES
                    (@BundleTransactionID, @RuleID, @RuleName, @DocumentType, @DocumentName, @DocumentID, 'PEND',GETDATE(),CURRENT_USER,GETDATE(),CURRENT_USER)

            END

    END

    END
END

In my code, if I execute with values, its not working and and insert fails with the following error :

Msg 515, Level 16, State 2, Procedure usp_CreateChecklist, Line 95 Cannot insert the value NULL into column 'Orig_Tran_Action_Rule_id', table 'EIC_ECM_Checklist.ECM.Assoc_Orig_Tran_TXN'; column does not allow nulls. INSERT fails. The statement has been terminated.

Msg 515, Level 16, State 2, Procedure usp_CreateChecklist, Line 104 Cannot insert the value NULL into column 'Bundle_Action_Rule_ID', table 'EIC_ECM_Checklist.ECM.Bundle_Checklist_TXN'; column does not allow nulls. INSERT fails. The statement has been terminated.

Msg 515, Level 16, State 2, Procedure usp_CreateChecklist, Line 117 Cannot insert the value NULL into column 'Document_Action_Rule_ID', table 'EIC_ECM_Checklist.ECM.Document_Checklist_TXN'; column does not allow nulls. INSERT fails. The statement has been terminated.

But if I execute individually each line and its working and all values getting stored in the table. All insert statement works. Tried debugging with individual statement. Like below :

SELECT 
    DATEADD(DAY, Number_Of_Days_Till_Next_Action, '2015-10-18T00:00:00'),
    rule1.Action_Rule_ID, rule1.Action_Rule_Name, 
    rule1.Current_Action_ID 
FROM 
    ecm.Action_Rule rule1
INNER JOIN 
    ecm.Action_Rule_Group rulegroup ON rulegroup.Action_Rule_Group_ID = rule1.Action_Rule_Group_ID
INNER JOIN 
    ref.Line_of_Business lob ON lob.Line_of_Business_Code = rulegroup.Line_of_Business_Code 
INNER JOIN 
    ref.State state ON state.State_Alpha_Code=rulegroup.State_Alpha_Code
INNER JOIN 
    ref.Source_System sourcesystem ON sourcesystem.Source_System_ID = rulegroup.Source_System_ID
INNER JOIN 
    ecm.[Document Catgeory] documentcategory ON documentcategory.Doc_Categ_ID = rulegroup.Doc_Categ_ID
                INNER JOIN ecm.[Document Type] documenttype ON documenttype.Doc_Type_ID=rule1.Doc_Type_ID
                INNER JOIN ecm.Exec_Acct_Group occupationgroup ON occupationgroup.Exec_Acct_Grp_ID = rulegroup.Exec_Acct_Grp_ID  
                INNER JOIN [ECM].[Bundle] bundle ON bundle.Bundle_ID = rule1.Bundle_ID 

            WHERE 
                bundle.Bundle_Name = 'Auto'
                AND rulegroup.Line_of_Business_Code='A'
                AND rulegroup.State_Alpha_Code='FL'
                AND documentcategory.Doc_Categ_Name=' review'
                AND rulegroup.Exec_Acct_Grp_ID=2
                AND rule1.Transaction_Flow='Outgoing doc' 
                AND sourcesystem.Source_System_Name = 'V4 Policy' 
                AND rulegroup.Doc_Categ_ID=documentcategory.Doc_Categ_ID
                AND documenttype.Doc_Type_Name ='Application'   

                SELECT Assoc_Orig_Tran_ID FROM [ECM].[Assoc_Orig_Tran_TXN] WHERE Assoc_Orig_Tran_ID='BPA201607131452113541050525A1REN'

                INSERT INTO  [ECM].[ASsoc_Orig_Tran_TXN] 
                    (Orig_Tran_Action_Rule_id, ASsoc_Orig_Tran_ID, ASsoc_Orig_Status_Code, First_Rule_Date, Final_Actionable_Date, Folder_ID,CREATED_DTM,CREATE_PROCESSNAME,UPDATE_DTM,UPDATE_PROCESSNAME)
                VALUES 
                    ('157','BPA201607131452113541050525A1REN', 'PEND','2015-10-18T00:00:00' , '2015-11-07 00:00:00.000',NULL,GETDATE(),CURRENT_USER,GETDATE(),CURRENT_USER)

                    SELECT originating_tran_id FROM ecm.bundle_checklist_txn WHERE originating_tran_id = 'BPA201607131452113541050525A1REN' AND bundle_name = 'Auto'


INSERT INTO  [ECM].[Bundle_Checklist_TXN] 
                    ( Bundle_Action_Rule_ID,Bundle_Name,Bundle_Status_Code,ASsoc_Orig_Tran_ID,Originating_Tran_ID,Doc_Categ_Name, Next_Action_Dt,CREATED_DTM,CREATE_PROCESSNAME,UPDATE_DTM,UPDATE_PROCESSNAME)
                VALUES 
                    ('157','Auto','PEND','BPA201607131452113541050525A1REN','BPA201607131452113545050525A1AMD','Underwriting review','2015-11-07 00:00:00.000',GETDATE(),CURRENT_USER,GETDATE(),CURRENT_USER)

declare     @BundleTransactionID int        
SELECT @BundleTransactionID=bundlechecklist.bundle_txn_id FROM [ECM].[Bundle_Checklist_TXN] bundlechecklist
                    INSERT INTO [ECM].[Document_Checklist_TXN]
                        (bundle_txn_id, document_action_rule_id, action_rule_name, doc_type_name, cp_document_name, document_id, doc_status_code,CREATED_DTM,CREATE_PROCESSNAME,UPDATE_DTM,UPDATE_PROCESSNAME)
                 VALUES
                    (@BundleTransactionID, '157', 'FL  Application', 'Application', 'CPLProp_Acknowledgment_FAQs', '321Z01W_007624ZRM00002G', 'PEND',GETDATE(),CURRENT_USER,GETDATE(),CURRENT_USER)

Any idea where it fails?

Answer 1

First Error says that you are trying to insert null value to Orig_Tran_Action_Rule_id but this field is NOT NULL so u cannot insert the data.

 INSERT INTO  [ECM].[ASsoc_Orig_Tran_TXN] 
                    (Orig_Tran_Action_Rule_id, Assoc_Orig_Tran_ID, Assoc_Orig_Status_Code, First_Rule_Date, Final_Actionable_Date, Folder_ID,CREATED_DTM,CREATE_PROCESSNAME,UPDATE_DTM,UPDATE_PROCESSNAME)
                VALUES 
                    (@RuleID,@AssociatedOriginatingTransactionID, 'PEND', @EffectiveOrCreationDate, @FinalDate,NULL,GETDATE(),CURRENT_USER,GETDATE(),CURRENT_USER)

Please check @RuleID.

And for other two also the same issue.

And you are right, when you insert individual at that time you provide values('157') instead of Variables(@RuleID). So it works fine.