SQL Procedure has no parameters and arguments were supplied

Question

So here is the code for the stored procedure and my execution. I keep getting this message when I try to execute my command:

Msg 8146, Level 16, State 2, Procedure sp_LabelFilm, Line 0
Procedure sp_LabelFilm has no parameters and arguments were supplied.

Any idea why? I am trying to update a column in the table tblfilm to say if a movie is short, medium, or long based on its run time.

ALTER PROCEDURE [dbo].[sp_LabelFilm]
AS
BEGIN
    DECLARE @Minutes INT, @duration char(10)

    DECLARE Filmcursor CURSOR FOR 
        (SELECT filmruntimeminutes, Duration FROM tblFilm)

    OPEN filmcursor

    FETCH NEXT FROM filmcursor INTO @duration

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SELECT @Minutes = FilmRunTimeMinutes FROM tblFilm

        IF @Minutes < 120 
            SET @duration = 'short' 
        ELSE IF @Minutes < 150
            SET @duration = 'medium'
        ELSE
            SET @duration = 'long'

        FETCH NEXT FROM filmcursor INTO @duration

        UPDATE tblFilm 
        SET Duration = @duration
    END

    CLOSE filmcursor
    DEALLOCATE filmcursor
END



DECLARE @Minutes INT, @duration CHAR(10)

EXECUTE [dbo].[sp_LabelFilm] @minutes, @duration

Answer 1

the error means exactly what it says. That you are passing arguments (variables @minutes and @duration) but there are no parameters defined on the stored procedure.

To declare parameters (input variables) you actually declare them before the AS like so:

use Movies
go

alter PROC [dbo].[sp_LabelFilm]
    @Minutes INT
    ,@duration CHAR(10)
AS

BEGIN


DECLARE Filmcursor  CURSOR 
......

Notice you don't need to use the key word DECLARE and once they are a declared as parameters you don't actually need to declare them again.

Next I am not totally sure what you are attempting to accomplish with the parameters in the stored procedure but it actually looks like you don't want to pass them but rather you want to get them as out put which would be like this:

use Movies
go

alter PROC [dbo].[sp_LabelFilm]
    @Minutes INT OUTPUT
    ,@duration CHAR(10) OUTPUT
AS

BEGIN


DECLARE Filmcursor  CURSOR 
....

And your execution statement would look like this:

declare @Minutes INT, @duration char(10)
execute [dbo].[sp_LabelFilm] @minutes = @Minutes OUTPUT, @duration = @duration OUTPUT