CODEWITHSUNDEEP
sqlsql-server-2008-r2
roger bulawan
roger bulawan

Reputation: 167

The formal parameter “@mode” was not declared as an OUTPUT parameter, but the actual parameter passed in requested output

I have this stored procedure:

ALTER PROCEDURE spCertificationType 
    @result nvarchar(15) output,
    @mode int 
AS
BEGIN
    if @mode = 1
    begin
        exec spGeneratedID 2, @result output
        print @result
    end
END

but when I tried to execute it,it has this error

The formal parameter “@mode” was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.

I tried to set @mode as output like this:

ALTER PROCEDURE spCertificationType 
    @result nvarchar(15) output,
    @mode int output
AS
BEGIN
    if @mode = 1
    begin
        exec spGeneratedID 2, @result output
        print @result
    end
END

but the it returns a null value.

Any fix for this? Thanks in advance.

Upvotes: 12

Views: 37869

Answers (4)

schmiddy98
schmiddy98

Reputation: 131

You'll also get this error if you didn't declare the parameter as OUTPUT in the stored procedure, but when you execute the stored procedure you use the OUTPUT keyword when specifying the parameter (or, in my case, using it in .NET code and specifying that it is an output parameter there).

Upvotes: 0

Vivek Nuna
Vivek Nuna

Reputation: 1

In my case, I had declared the OUTPUT parameter last but was also getting a similar exception.

I moved the default parameters (like @var FLOAT = null) to the end after the output parameter then it started working.

Upvotes: 0

user7502347
user7502347

Reputation: 11

I fixed this error a different way.

  1. I had removed the OUTPUT statement after a parameter in my SP.
  2. I then got the error.
  3. I then went back to the SQLDataSource Configure Data Source wizard and went through the steps again. I discovered that changing the SP made the wizard delete the settings associated with the parameter that used to have OUTPUT after it.

Upvotes: 1

david sam
david sam

Reputation: 531

the sequence of parameter in store procedure is that first use input parameter then use output parameter:- you can see this link for more knowledge of store procedure:-

http://www.codeproject.com/Articles/126898/Sql-Server-How-To-Write-a-Stored-Procedure-in-SQL 

ALTER PROCEDURE spCertificationType 

     @mode int,
     @result nvarchar(15) output
 AS
 BEGIN
     if @mode = 1
   begin
    exec spGeneratedID 2, @result output
    print @result
   end
END

Upvotes: 12

Related Questions