获取"Procedure of function expects was Procedure“错误，但提供了所有参数

Question

我正在尝试编写一个存储过程，一次将数据添加到三个表中。存储过程调用其他三个存储过程。我有每个参数的数据，但我在SQL Server中不断收到缺少参数的错误。问题是错误消息中给出的参数不是主存储过程的EXECUTE语句的一部分。运行sp_help会显示我正在将所有需要的参数传递给main过程。代码如下。

Error message: Msg 201, Level 16, State 4, Procedure uspAddCustomerJob, Line 0 [Batch Start Line 157]
Procedure or function 'uspAddCustomerJob' expects parameter '@intJobID', which was not supplied.
-- =========================================

-- stored procedure to add customer record to customer table

GO

CREATE PROCEDURE uspAddCustomer
     @intCustomerID AS INTEGER = 0 OUTPUT
    ,@strName AS VARCHAR(250)
    ,@strPhone AS VARCHAR(250)
    ,@strEmail AS VARCHAR(250)
AS
SET XACT_ABORT ON

BEGIN TRANSACTION

    INSERT INTO TCustomers(strName, strPhone, strEmail)
    VALUES (@strname, @strPhone, @strEmail)

COMMIT TRANSACTION

GO

-- =================================================

-- stored procedure to add job to job table

GO

CREATE PROCEDURE uspAddJob
     @intJobID AS INTEGER = 0 OUTPUT
    ,@strJobDescription AS VARCHAR(250)
    ,@dtmStartDate AS DATETIME
    ,@dtmEndDate AS DATETIME
AS
SET XACT_ABORT ON

BEGIN TRANSACTION

    INSERT INTO TJobs(strJobDescription, dtmStartDate, dtmEndDate)
    VALUES (@strJobDescription, @dtmStartDate, @dtmEndDate)

COMMIT TRANSACTION

GO

-- ===================================================

-- stored procedure to add PK's from previous tables to a third table (many-to-many relationship)

GO

CREATE PROCEDURE uspAddCustomerJob 
     @intCustomerJobID AS INTEGER OUTPUT
    ,@intCustomerID AS INTEGER 
    ,@intJobID AS INTEGER

AS
SET XACT_ABORT ON

BEGIN TRANSACTION

    INSERT INTO TCustomerJobs(intCustomerID, intJobID)
    VALUES (@intCustomerID, @intJobID)

COMMIT TRANSACTION

GO

-- =====================================================

-- main stored procedure that calls each component stored procedure, with execution code at the bottom. Running the EXECUTE statement yields the error mentioned above.

GO

CREATE PROCEDURE uspAddCustomerAndJob --main procedure 
     @intCustomerJobID      AS INTEGER  = 0 OUTPUT
    ,@strName               AS VARCHAR(250)
    ,@strPhone              AS VARCHAR(250)
    ,@strEmail              AS VARCHAR(250)
    ,@strJobDescription     AS VARCHAR(250)
    ,@dtmStartDate          AS DATETIME
    ,@dtmEndDate                AS DATETIME

AS
SET XACT_ABORT ON

BEGIN TRANSACTION

    DECLARE @intCustomerID AS INTEGER   = 0
    DECLARE @intJobID AS INTEGER            = 0

    EXECUTE uspAddCustomer @intCustomerID OUTPUT, @strName, @strPhone, @strEmail;

    EXECUTE uspAddJob @intJobID OUTPUT, @strJobDescription, @dtmStartDate, @dtmEndDate;

    EXECUTE uspAddCustomerJob @intCustomerJobID OUTPUT, @intJobID;

COMMIT TRANSACTION

GO

--TEST CODE
DECLARE @intCustomerJobID AS INTEGER;   
EXECUTE uspAddCustomerAndJob @intCustomerJobID OUTPUT, 'Joe Smith', '513-555-9644', 'Jsmith@yahoo.com', 'Fix cracked sewer pipe', '6/1/2019', '6/5/2019'

Answer 1

您执行的过程中发生错误。

"uspAddCustomerJob“的过程定义有3个参数：

CREATE PROCEDURE uspAddCustomerJob 
     @intCustomerJobID AS INTEGER OUTPUT
    ,@intCustomerID AS INTEGER 
    ,@intJobID AS INTEGER

但它是用2调用的，缺少"@intCustomerID“：

EXECUTE uspAddCustomerJob @intCustomerJobID OUTPUT, @intJobID;

Answer 2

该错误消息具有误导性。在uspAddCustomerAndJob中，您缺少一个参数。

您的呼叫如下所示：

EXECUTE uspAddCustomerJob @intCustomerJobID OUTPUT, @intJobID;

SQL Server将获取您的第一个整数参数，并将其映射到子进程的第一个整数参数，因此它认为它正在获取@intCustomerID。

您可以通过在调用中显式地命名您的参数来避免这种混乱，您无论如何都应该这样做，以方便可能需要在某天凌晨3:00AM对代码进行故障排除的任何人(包括您自己)。所以它看起来像这样：

EXECUTE uspAddCustomerJob 
  @intCustomerJobID = @intCustomerJobID OUTPUT, 
  @intCustomerID = @intCustomerID
  @intJobID = @intJobID;

当您的局部变量的名称与参数的名称相同时，这看起来很有趣，但在适当的情况下，您可以灵活地对它们进行不同的命名。此外，没有遗漏的参数惊喜，或者，至少，关于哪一个遗漏了更好的消息。