Pfthb

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

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

I had defined the parameters on the stored procedure, before the AS, but still I was facing the same problem until I realized that the procedure had 'create' instead of 'alter'. Changing it to alter procedure worked for me.(Faced this issue while I was trying to debug).

Apart from the first answer which is apt - In my case I did not have any parameters and while EXEC was getting a similar error.

However the difference being - I was putting a "go" below the EXEC statement.

After removing the go it was executed properly.