sql server - Executing stored procedure not working but individual statements working in stored procedure -

i have following stored procedure trying 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  ) 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 (select  bundlechecklist.bundle_txn_id, documentchecklist.doc_checklist_txn_id, documentchecklist.doc_type_name ecm.bundle_checklist_txn bundlechecklist                 left outer join [ecm].[document_checklist_txn] documentchecklist on documentchecklist.bundle_txn_id = bundlechecklist.bundle_txn_id                 bundlechecklist.originating_tran_id = @originatingtransactionid , bundle_name = @bundlename)t t.doc_checklist_txn_id not null )                  begin                      update ecm.bundle_checklist_txn                      set bundle_status_code = 'comp'                     ecm.bundle_checklist_txn bundlechecklist                     inner join (select distinct bundle_txn_id,document_id ecm.document_checklist_txn documentchecklist                     doc_status_code='comp') comp                     on comp.bundle_txn_id = bundlechecklist.bundle_txn_id                     , comp.bundle_txn_id not in (                     select bundle_txn_id ecm.document_checklist_txn documentchecklist                     documentchecklist.bundle_txn_id=comp.bundle_txn_id , documentchecklist.doc_status_code<>'comp')                      , comp.document_id=@documentid                     bundlechecklist.bundle_txn_id= @originatingtransactionid                     , bundlechecklist.assoc_orig_tran_id=@associatedoriginatingtransactionid                      update associatedtransaction                     set assoc_orig_status_code='comp'                     [ecm].[assoc_orig_tran_txn] associatedtransaction                     inner join (select distinct assoc_orig_tran_id [ecm].[bundle_checklist_txn] bundlechecklist                     bundlechecklist.[bundle_status_code]='comp')comp                     on comp.assoc_orig_tran_id=associatedtransaction.assoc_orig_tran_id                     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 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                                bundle.bundle_name = @bundlename                 , rulegroup.line_of_business_code=@lineofbusiness                 , rulegroup.state_alpha_code=@riskstate                 , documentcategory.doc_categ_name=@documentcategory                 , rulegroup.exec_acct_grp_id=@occupationcode                 , rule1.transaction_flow=@transactionflow                  , sourcesystem.source_system_name = @sourcesystem                  , rulegroup.doc_categ_id=documentcategory.doc_categ_id                 , documenttype.doc_type_name =@documenttype              if not exists (select assoc_orig_tran_id [ecm].[assoc_orig_tran_txn] assoc_orig_tran_id=@associatedoriginatingtransactionid)             begin                 insert  [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 ecm.bundle_checklist_txn originating_tran_id = @originatingtransactionid , bundle_name = @bundlename)             begin                 insert  [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 (select  bundlechecklist.bundle_txn_id, documentchecklist.doc_checklist_txn_id, documentchecklist.doc_type_name ecm.bundle_checklist_txn bundlechecklist             left outer join [ecm].[document_checklist_txn] documentchecklist on documentchecklist.bundle_txn_id = bundlechecklist.bundle_txn_id                 bundlechecklist.originating_tran_id = @originatingtransactionid , bundle_name = @bundlename)t t.doc_checklist_txn_id not null )              begin                 select @bundletransactionid=bundlechecklist.bundle_txn_id [ecm].[bundle_checklist_txn] bundlechecklist                     insert [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 code, if execute values, not working , and insert fails following error :

msg 515, level 16, state 2, procedure usp_createchecklist, line 95 cannot insert value null column 'orig_tran_action_rule_id', table 'eic_ecm_checklist.ecm.assoc_orig_tran_txn'; column not allow nulls. insert fails. statement has been terminated.

msg 515, level 16, state 2, procedure usp_createchecklist, line 104 cannot insert value null column 'bundle_action_rule_id', table 'eic_ecm_checklist.ecm.bundle_checklist_txn'; column not allow nulls. insert fails. statement has been terminated.

msg 515, level 16, state 2, procedure usp_createchecklist, line 117 cannot insert value null column 'document_action_rule_id', table 'eic_ecm_checklist.ecm.document_checklist_txn'; column not allow nulls. insert fails. statement has been terminated.

but if execute individually each line , working , values getting stored in table. insert statement works. tried debugging individual statement. 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       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                                bundle.bundle_name = 'auto'                 , rulegroup.line_of_business_code='a'                 , rulegroup.state_alpha_code='fl'                 , documentcategory.doc_categ_name=' review'                 , rulegroup.exec_acct_grp_id=2                 , rule1.transaction_flow='outgoing doc'                  , sourcesystem.source_system_name = 'v4 policy'                  , rulegroup.doc_categ_id=documentcategory.doc_categ_id                 , documenttype.doc_type_name ='application'                     select assoc_orig_tran_id [ecm].[assoc_orig_tran_txn] assoc_orig_tran_id='bpa201607131452113541050525a1ren'                  insert  [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 ecm.bundle_checklist_txn originating_tran_id = 'bpa201607131452113541050525a1ren' , bundle_name = 'auto'   insert  [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 [ecm].[bundle_checklist_txn] bundlechecklist                     insert [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 fails?

first error says trying insert null value orig_tran_action_rule_id field not null u cannot insert data.

 insert  [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 other 2 same issue.

and right, when insert individual @ time provide values('157') instead of variables(@ruleid). works fine.