How Do I Solve Error "Expects Parameter" for stored procedure

Question

I am getting this error

Msg 201, Level 16, State 4, Procedure sp_GetAllAirports, Line 0 [Batch Start Line 2]
Procedure or function 'sp_GetAllAirports' expects parameter '@AirportID', which was not supplied."

When I run

EXEC sp_GetAllAirports

The following is my stored procedure which shows @AirportID, what could be the issue?

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

Pass an AirportID (sp_GetAllAirports 1234) or give it a default (@AirportID INT = 1234)?. Your Stored Procedure expects arguments. (AirportID, ICAOCode, AirportName, City, Lat, Long, Elevation and Country) though it doesn't use any of them. You're better off removing the arguments so you won't have to pass any either. — RobIII
From MS documentation: "Avoid the use of the sp_ prefix when naming procedures. This prefix is used by SQL Server to designate system procedures. Using the prefix can cause application code to break if there is a system procedure with the same name." As well as performance issues. — HABO
Why not use an inner join here? You have a left join but then your where clause turns into an inner join. Remove the where clause and use an inner join and you will get the same results. But where are your parameters in your query? Why pass them in if you don't use them. Either add them to your query appropriately or remove them from the procedure definition. — Sean Lange

RobIII RobIII · Accepted Answer · 2019-11-05T16:43:48

Your stored procedure is defined to expect arguments (AirportID, ICAOCode, AirportName, City, Lat, Long, Elevation and 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  
...

However, it doesn't use any of them. So you probably just need to remove them:

CREATE PROCEDURE [dbo].[sp_GetAllAirports]

AS  
...

Alternatively, make sure the arguments are used in the SP (so it makes sense to expect arguments) and pass values accordingly (sp_GetAllAirports 1234) e.g.:

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

....

Or, finally, give the arguments default values, e.g:

CREATE PROCEDURE [dbo].[sp_GetAllAirports]

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

AS
...

That way you won't have to explicitly pass any argument values. However, you'll still need to use the arguments to having the arguments make sense in the first place.

How Do I Solve Error "Expects Parameter" for stored procedure

1 Answers