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?

well, you could start by showing us how you are executing that sp — Lamak
paste it in ssms, hit f5, double click the error and that should take you to the line where error occurred.. — anish
Seemingly, your variables aren't being set. I would say that you need to check your logic, and add that to your debugging effort. Maybe put some print statements in your proc to be sure of what your if-thens are doing. Also, when setting variables with a select statement, you should be certain that your where clause will only select one row. — Tim Lehner

Hardik Patel Hardik Patel · Accepted Answer · 2016-07-26T10:08:57

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.

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

1 Answers