如何解决存储过程的错误“期望参数”

2024-05-02 • 问答

我收到此错误

Msg 201，级别16，状态4，过程sp_GetallAirports，第0行[批处理开始第2行]
过程或函数'sp_GetallAirports'需要未提供的参数'@AirportID'。”

我跑步时

EXEC sp_GetallAirports

以下是我的存储过程，其中显示了@AirportID，可能是什么问题？

IF OBJECT_ID('sp_GetallAirports','P') IS NOT NULL
    DROP PROCEDURE [dbo].[sp_GetallAirports]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_GetallAirports]
    @AirportID INT,@ICAOCode VARCHAR(4) NULL,@AirportName VARCHAR(MAX),@City VARCHAR(MAX),@Lat DECIMAL(8,3),@Long DECIMAL (11,@Elevation INT,@Country NVARCHAR(MAX)
AS  
BEGIN TRANSactION
BEGIN TRY
    SET NOCOUNT ON  
    SET ANSI_WARNINGS OFF  
    SET TRANSactION ISOLATION LEVEL READ UNCOMMITTED  

    --SELECT * FROM tbl_Airports ORDER BY AirportID ASC

    SELECT 
        AirportID,ICAOCode,AirportName,City,Latitude,Longitude,Elevation,CountryFK
    FROM 
        tbl_Airports 
    LEFT JOIN 
        tbl_Countries ON CountryID = tbl_Airports.CountryFK
    WHERE 
        CountryID = tbl_Airports.CountryFK
    ORDER BY 
        AirportID
END TRY
BEGIN CATCH
    DeclARE @ErMessage NVARCHAR(MAX),@ErSeverity INT,@ErState INT

    SELECT 
        @ErMessage = ERROR_MESSAGE(),@ErSeverity = ERROR_SEVERITY(),@ErState = ERROR_STATE()

    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSactION
    END

    RAISERROR(@ErMessage,@ErSeverity,@ErState)
END CATCH

IF @@TRANCOUNT > 0
BEGIN
    COMMIT TRANSactION
END
GO

您的存储过程已定义为期望参数（AirportID，ICAOCode，AirportName，City，Lat，Long，{{ 1}}和Elevation）：

Country

但是，它不使用任何一个。因此，您可能只需要删除它们：

CREATE PROCEDURE [dbo].[sp_GetAllAirports]

@AirportID INT,@ICAOCode VARCHAR(4) NULL,@AirportName VARCHAR(MAX),@City VARCHAR(MAX),@Lat DECIMAL(8,3),@Long DECIMAL (11,@Elevation INT,@Country NVARCHAR(MAX)


AS  
...

或者，请确保在SP中使用了参数（这样可以合理地期望参数）并相应地传递值（CREATE PROCEDURE [dbo].[sp_GetAllAirports] AS ...），例如：

sp_GetAllAirports 1234

或者，最后，为参数提供默认值，例如：

CREATE PROCEDURE [dbo].[sp_GetAllAirports]

@AirportID INT

AS  

....

SELECT AirportID,ICAOCode,AirportName,City,Latitude,Longitude,Elevation,CountryFK
FROM tbl_Airports 
LEFT JOIN tbl_Countries ON CountryID = tbl_Airports.CountryFK
WHERE CountryID = tbl_Airports.CountryFK
    AND AirportID = @AirportID  -- Using first argument here
ORDER BY AirportID

....

这样，您将不必显式传递任何参数值。但是，您仍然需要使用参数使这些参数首先有意义。

如何解决存储过程的错误“期望参数”

yanyan20080909 回答：如何解决存储过程的错误“期望参数”

大家都在问