时间戳

首页 > 解决方案 > 如何解决存储过程的错误“期望参数”

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

问题描述

我收到此错误

消息 201，级别 16，状态 4，过程 sp_GetAllAirports，第 0 行 [Batch Start Line 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,3),
    @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

标签： sql-servertsqlstored-procedures

解决方案

您的存储过程被定义为期望参数（AirportID, ICAOCode, AirportName, City, Lat, Long,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,3),
@Elevation INT ,
@Country NVARCHAR(MAX)


AS  
...

但是，它不使用其中任何一个。所以你可能只需要删除它们：

CREATE PROCEDURE [dbo].[sp_GetAllAirports]

AS  
...

或者，确保在 SP 中使用参数（因此期望参数是有意义的）并相应地传递值（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

....

或者，最后，给参数默认值，例如：

CREATE PROCEDURE [dbo].[sp_GetAllAirports]

@AirportID INT = NULL,
@ICAOCode VARCHAR(4) = 'FOO',
...

AS
...

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

推荐阅读