如何将程序从一个调用到另一个 [英] how to call procedure fron one to another

问题描述

Hello Team，

这是我的一个存储过程如下：

  ALTER   PROCEDURE  [dbo]。[UpdateCustomerLoanOCR] 
  @ CUSTOMERLOANOCRID   int ，
  @ UPDATEDBY   int ，
  @ UPDATEDON   datetime  
  AS  
  BEGIN  
  update  CUSTOMERLOANOCR  WITH （ROWLOCK）
  set  
 processed =  1 ， 
 UpdatedBy =  @ UPDATEDBY ，
 UpdatedOn =  @ UPDATEDON  
 < span class =code-keyword>其中 id =  @CUSTOMERLOANOCRID  
 
 
 结束 

这是我的第二个存储过程如下

 创建  PROCEDURE  InsertRecordInAuditCustomerLoanData 
  @ CustomerLoanDataID   INT  
  AS  
  BEGIN  
 将 插入 AUDIT_CUSTOMERLOANDATA（CUSTOMERLOANDATAID，BATCHID，FILELOGID，[FILENAME]，RECORDNO，FIRSTNAME，MIDDLENAME，LASTNAME，MOTHERNAME，FULLADDRESS，MOBILENO1，EMAILID，
 DOB，LOAN1INTRATE，LOAN1AMT，LOAN2INTRATE，LOAN2AMT，DATEOFEMI，
 RELEASEDATE，HOUSETYPE，SALARY，CUSTOMERLOANOCRID，CUSTOMERADDR1，
 CUSTOMERADDR2，LANDMARK，STATE，CITY，PINCODE，
 ADDRVERIFY，PHOTOVER IFY，GENDER，LANDLINENO，MOBILENO2，COMPANYNAME，CREATEDBY，
 CREATEDON，QCTOVERIFYREMARKS，ISD1，ISD2，STD1，QCPROCESSED）
 选择 ID，BATCHID ，FILELOGID，[FILENAME]，RECORDNO，FIRSTNAME，MIDDLENAME，LASTNAME，MOTHERNAME，FULLADDRESS，MOBILENO1，EMAILID，
 DOB，LOAN1INTRATE，LOAN1AMT，LOAN2INTRATE，LOAN2AMT，DATEOFEMI，
 RELEASEDATE，HOUSETYPE，SALARY，CUSTOMERLOANOCRID ，CUSTOMERADDR1，
 CUSTOMERADDR2，LANDMARK，STATE，CITY，PINCODE，
 ADDRVERIFY，PHOTOVERIFY，GENDER，LANDLINENO，MOBILENO2，COMPANYNAME，CREATEDBY，
 CREATEDON，QCTOVERIFYREMARKS，ISD1，ISD2，STD1，QCPROCESSED 
 来自 CUSTOMERLOANDATA（ nolock ）
 其中 id =  @ CustomerLoanDataID  
 
 选择  @ CustomerLoanDataID  
 
  end  

这是我的第三个存储过程

  ALTER   PROCEDURE  [dbo]。[AddCustomerLoanData] 
  @ BATCHID   int ，
  @ FILELOGID   int ，
  @ FILENAME   varchar （max），
  @ RECORDNO   int ，
  @ FIRSTNAME   varchar （ 25 ），
  @ MIDDLENAME   varchar （ 25 ），
  @ LASTNAME   varchar （ 25 ），
  @ MOTHERNAME   varchar （ 25 ），
  @ FULLADDRESS   varchar （max），
  @ MOBILENO   varchar （ 15 ）， 
  @ EMAILID   varchar （ 50 ），
  @ DOB   datetime  =  NULL ，
  @ LOAN1INTRATE   decimal （ 18 ， 2 ），
  @ LOAN1AMT   decimal （ 18 ， 2 ），
  @ LOAN2INTRATE   decimal （ 18 ， 2 ），
  @ LOAN2AMT   decimal （ 18 ， 2 ），
  @ DATEOFEMI  datetime = < span class =code-keyword> NULL ，
  @ RELEASEDATE  datetime =  NULL ，
  @ HOUSETYPE   varchar （ 15 ），
  @ SALARY   decimal （ 18 ， 2 ），
  @ CUSTOMERLOANOCRID   int ，
  @ CUSTOMERADDR1   varch ar （ 100 ），
  @ CUSTOMERADDR2   varchar （ 100 ），
  @ LANDMARK   varchar （ 50 ），
  @ STATE  < span class =code-keyword> varchar （ 20 ），
  @ CITY   varchar （ 20 ），
  @ PINCODE   varchar （ 7 ），
  @ADDRVERIFY   varchar （ 3 ），
  @ PHOTOVERIFY   varchar （ 3 ），
  @GENDER   varchar （ 6 ），
  @ LANDLINENO   varchar （ 15 ），
  @ MOBILENO2   varchar （ 15 ），
 < span class =code-sdkkeyword> @ COMPANYNAME   varchar （ 50 ），
  @ CREATEDBY   int ，
  @ CREATEDON   datetime ，
  @ QCTOVERIFYREMARKS   varchar （max），
  @ ISD1   varchar （ 5 ），
  @ ISD2   varchar （ 5 ），
  @ STD1   varchar （ 5 ），
  @ ID   INT  OUT 
  AS  
  BEGIN  
 
 
 插入 进入 
 CUSTOMERLOANDATA（BATCHID，FILELOGID，[ FILENAME]，RECORDNO，FIRSTNAME，MIDDLENAME，LASTNAME，MOTHERNAME，FULLADDRESS，MOBILENO1，EMAILID，
 DOB，LOAN1INTRATE，LOAN1AMT，LOAN2INTRATE，LOAN2AMT，DATEOFEMI，
 RELEASEDATE，HOUSETYPE，SALARY，CUSTOMERLOANOCRID，CUSTOMERADDR1，
 CUSTOMERADDR2，LANDMARK，STATE，CITY，PINCODE，
 ADDRVERIFY，PHOTOVERIFY，GENDER，LANDLINENO，MOBILENO2，COMPANYNAME，CREATEDBY，CR EATEDON，QCTOVERIFYREMARKS，
 ISD1，ISD2，STD1）
 值（ @ BATCHID  ， @ FILELOGID ， @ FILENAME ， @ RECORDNO ， @ FIRSTNAME ， @ MIDDLENAME ， @ LASTNAME ， @ MOTHERNAME ， @ FULLADDRESS ， @ MOBILENO ， @ EMAILID ，
  @ DOB ， @ LOAN1INTRATE ， @ LOAN1AMT ， @ LOAN2INTRATE ， @ LOAN2AMT ， @ DATEOFEMI ，
  @ RELEASEDATE ， @ HOUSETYPE ， @ SALARY  ， @ CUSTOMERLOANOCRID ， @ CUSTOMERADDR1 ，
  @ CUSTOMERADDR2 ， @ LANDMARK ， @ STATE ， @CITY ， @ PINCODE ，
  @ ADDRVERIFY ， @ PHOTOVERIFY ， @ GENDER ， @ LANDLINENO ， @ MOBILENO2 ， @ COMPANYNAME ， @ CREATEDBY ， @ CREATEDON ， @ QCTOVERIFYREMARKS ，
  @ ISD1 ， @ ISD2 ， @ STD1  ）
  SET   @ ID  =  @@ IDENTITY ; 
 
  exec  UpdateCustomerLoanOCR 
  exec  InsertRecordInAuditCustomerLoanData 
 
 结束 

我想调用第一个和第二个存储过程第三个过程。当我在第三个过程中调用第一个和第二个存储过程但是它给出了错误，过程或函数'UpdateCustomerLoanOCR'期望参数'@UPDATEDBY'，这是未提供的。由于UpdateCustomerLoanOCR中存在三个参数，一个参数出现在InsertRecordInAuditCustomerLoanData中。请告诉我应该如何解决这个问题。这是从一个到另一个调用程序的正确方法吗？



谢谢

Harshal

解决方案

您尚未提供以下程序的参数

1. [UpdateCustomerLoanOCR]
2.InsertRecordInAuditCustomerLoan数据

Hello Team,
This is my one stored procedure as follow:

ALTER PROCEDURE [dbo].[UpdateCustomerLoanOCR]
@CUSTOMERLOANOCRID int,
@UPDATEDBY int,
@UPDATEDON datetime
AS
BEGIN
update CUSTOMERLOANOCR WITH (ROWLOCK)
set
processed = 1,
UpdatedBy = @UPDATEDBY,
UpdatedOn  = @UPDATEDON
where  id = @CUSTOMERLOANOCRID


End

This is my second Stored Procedure as follows

CREATE PROCEDURE InsertRecordInAuditCustomerLoanData
@CustomerLoanDataID INT
AS
BEGIN
Insert into AUDIT_CUSTOMERLOANDATA (CUSTOMERLOANDATAID,BATCHID,FILELOGID,[FILENAME],RECORDNO,FIRSTNAME,MIDDLENAME, LASTNAME ,MOTHERNAME,FULLADDRESS ,MOBILENO1 ,EMAILID ,
          DOB, LOAN1INTRATE ,LOAN1AMT ,LOAN2INTRATE ,LOAN2AMT ,DATEOFEMI ,
          RELEASEDATE, HOUSETYPE ,SALARY ,CUSTOMERLOANOCRID ,CUSTOMERADDR1 ,
          CUSTOMERADDR2 ,LANDMARK ,STATE ,CITY ,PINCODE  ,
          ADDRVERIFY ,PHOTOVERIFY ,GENDER ,LANDLINENO ,MOBILENO2 ,COMPANYNAME,CREATEDBY,
CREATEDON,QCTOVERIFYREMARKS,ISD1,ISD2,STD1,QCPROCESSED)
   Select ID,BATCHID,FILELOGID,[FILENAME],RECORDNO,FIRSTNAME,MIDDLENAME, LASTNAME ,MOTHERNAME,FULLADDRESS ,MOBILENO1 ,EMAILID ,
          DOB, LOAN1INTRATE ,LOAN1AMT ,LOAN2INTRATE ,LOAN2AMT ,DATEOFEMI ,
          RELEASEDATE, HOUSETYPE ,SALARY ,CUSTOMERLOANOCRID ,CUSTOMERADDR1 ,
          CUSTOMERADDR2 ,LANDMARK ,STATE ,CITY ,PINCODE  ,
          ADDRVERIFY ,PHOTOVERIFY ,GENDER ,LANDLINENO ,MOBILENO2 ,COMPANYNAME,CREATEDBY,
CREATEDON,QCTOVERIFYREMARKS,ISD1,ISD2,STD1,QCPROCESSED
from CUSTOMERLOANDATA (nolock)
where id = @CustomerLoanDataID

select @CustomerLoanDataID

end

this is my third Stored Procedure

ALTER PROCEDURE [dbo].[AddCustomerLoanData] 
@BATCHID int,
@FILELOGID int,
@FILENAME varchar(max),
@RECORDNO int,
@FIRSTNAME varchar(25),
@MIDDLENAME varchar(25),
@LASTNAME varchar(25),
@MOTHERNAME varchar(25),
@FULLADDRESS varchar(max) ,
@MOBILENO varchar(15),
@EMAILID varchar(50),
@DOB datetime = NULL,
@LOAN1INTRATE decimal(18,2),
@LOAN1AMT decimal(18,2),
@LOAN2INTRATE decimal(18,2),
@LOAN2AMT decimal(18,2),
@DATEOFEMI datetime= NULL ,
@RELEASEDATE datetime= NULL,
@HOUSETYPE varchar(15),
@SALARY decimal(18,2),
@CUSTOMERLOANOCRID int,
@CUSTOMERADDR1 varchar(100),
@CUSTOMERADDR2 varchar(100),
@LANDMARK varchar(50),
@STATE varchar(20),
@CITY varchar(20),
@PINCODE varchar(7),
@ADDRVERIFY varchar(3),
@PHOTOVERIFY varchar(3),
@GENDER varchar(6),
@LANDLINENO varchar(15),
@MOBILENO2 varchar(15),
@COMPANYNAME varchar(50),
@CREATEDBY int,
@CREATEDON datetime,
@QCTOVERIFYREMARKS varchar(max),
@ISD1 varchar(5),
@ISD2 varchar(5),
@STD1 varchar(5),
@ID INT OUT
AS
BEGIN

	
insert into 
   CUSTOMERLOANDATA (BATCHID,FILELOGID,[FILENAME],RECORDNO,FIRSTNAME,MIDDLENAME, LASTNAME ,MOTHERNAME,FULLADDRESS ,MOBILENO1 ,EMAILID ,
                                  DOB, LOAN1INTRATE ,LOAN1AMT ,LOAN2INTRATE ,LOAN2AMT ,DATEOFEMI ,
                                  RELEASEDATE, HOUSETYPE ,SALARY ,CUSTOMERLOANOCRID ,CUSTOMERADDR1 ,
                                  CUSTOMERADDR2 ,LANDMARK ,STATE ,CITY ,PINCODE  ,
                                 ADDRVERIFY ,PHOTOVERIFY ,GENDER ,LANDLINENO ,MOBILENO2 ,COMPANYNAME,CREATEDBY, CREATEDON,QCTOVERIFYREMARKS,
                                     ISD1,ISD2,STD1)
             values(@BATCHID, @FILELOGID, @FILENAME, @RECORDNO, @FIRSTNAME, @MIDDLENAME, @LASTNAME, @MOTHERNAME ,@FULLADDRESS , @MOBILENO ,@EMAILID ,
								  @DOB ,@LOAN1INTRATE ,@LOAN1AMT ,@LOAN2INTRATE ,@LOAN2AMT ,@DATEOFEMI  ,
								  @RELEASEDATE ,@HOUSETYPE ,@SALARY ,@CUSTOMERLOANOCRID ,@CUSTOMERADDR1 ,
								  @CUSTOMERADDR2 ,@LANDMARK ,@STATE ,@CITY ,@PINCODE, 
								  @ADDRVERIFY ,@PHOTOVERIFY,@GENDER ,@LANDLINENO ,@MOBILENO2 ,@COMPANYNAME, @CREATEDBY ,@CREATEDON,@QCTOVERIFYREMARKS,
                                     @ISD1,@ISD2,@STD1 )
SET @ID = @@IDENTITY;

exec UpdateCustomerLoanOCR
exec InsertRecordInAuditCustomerLoanData

End

I want to call first and second Stored Procedure in Third procedure.As i call the first and second Stored Procedure in Third procedure but it gives me error the Procedure or function 'UpdateCustomerLoanOCR' expects parameter '@UPDATEDBY', which was not supplied.As there are three parameters are present in UpdateCustomerLoanOCR and one parameter is present in InsertRecordInAuditCustomerLoanData.Please kindly tell me how should i get this solve.Is this is the right way to call procedure from one to another?

Thanks
Harshal

解决方案

You have not provided the parameters for the following procedures
1.[UpdateCustomerLoanOCR]
2.InsertRecordInAuditCustomerLoanData

这篇关于如何将程序从一个调用到另一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！