none
Procedure or function 'theName' expects parameter @output which was not supplied RRS feed

  • Question

  • I have one doubt.

    I have 2 dbs

    they both use these connection string:

    Server=ServerName-QA; Database; db1Name; User ID=sa; Password=thePass
    Server=ServerName-QA; Database; db2Name; User ID=sa; Password=thePass

    I try to connect those dbs store procedures using this code:

    public static string execute(string cnnString, DateTime 
                                date, string storedProcedure)
    {
    
       string result = string.Empty;
       SqlConnection sqlConnection = new 
                                      SqlConnection(cnnString);
    
       try
       {
         SqlCommand cmd = new SqlCommand();
         cmd.CommandText = storedProcedure;
         cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@date", SqlDbType.DateTime).Value = date;
    
        cmd.CommandTimeout = 0;
        SqlParameter ouput = new SqlParameter("@ouput", SqlDbType.Int);
    
         ouput.Direction = ParameterDirection.Output;
         cmd.Parameters.Add(ouput);
         cmd.Connection = sqlConnection;
         sqlConnection.Open();
         cmd.ExecuteNonQuery();
         int resultNumber = int.Parse(cmd.Parameters["@ouput"].Value.ToString());
    
         if (resultNumber == 1 || resultNumber == 0)
                 result = resultNumber.ToString();
         else
                 result = Texts.error_unknown;
      }
      catch (Exception ex)
      {
        sqlConnection.Close();
        result = string.Format(Texts.error_storedProcedure, storedProcedure, ex.Message);
      }
      finally
      {
        sqlConnection.Close();
      }
      return result;
    }

    the store procedures for both dbs I am trying to run have this structure:


    the issue is:

    whenever I check to run the db1Name store procedure, the output is good

    But whenever I check to run the db2Name this message appears:
    Procedure or function '' expects parameter '@output', which was not supplied.

    do you know a possible solution to that?But when I check to run the db2Name this message appears:
    Procedure or function '' expects parameter '@output', which was not supplied.

    do you know a possible solution to that?


    • Edited by MarSteven Friday, May 3, 2019 10:21 PM
    Friday, May 3, 2019 10:20 PM

Answers

  • Look carefully at your C# code. The name of the parameter is "@ouput". But in your second SQL procedure it is called "@output". Note the missing "t" in the C# code.

    The other procedure uses @ouput (without the "t") and that's why this one works.

    • Marked as answer by MarSteven Monday, May 6, 2019 3:38 PM
    Sunday, May 5, 2019 8:35 PM
    Moderator

All replies

  • It would appear there is no code issue so can you provide the actual SQL for both Stored Procedures?

    In the mean time does it look like this?

    CREATE PROCEDURE usp_InsertContract
        @ContractNumber varchar(7),
        @NewId int OUTPUT
    AS
    BEGIN
        INSERT INTO dbo.Contracts (ContractNumber)
        VALUES (@ContractNumber)
    
        SELECT @NewId = SCOPE_IDENTITY()
    END


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, May 3, 2019 11:23 PM
    Moderator
  • For db1Name Sp:

    ALTER PROCEDURE [dbo].[spDb1]
    	@date DATETIME, @ouput int OUTPUT
    AS 
    BEGIN
    
             // Huge amount  lot of logic
    
    END

    For db2Name Sp:

    ALTER PROCEDURE [dbo].[spDb2]
    	@date DATETIME, @output int OUTPUT
    AS 
    BEGIN
            // More logic
    
    END

    I can't provide the current logic :/


    • Edited by MarSteven Friday, May 3, 2019 11:36 PM
    Friday, May 3, 2019 11:35 PM
  • For db1Name Sp:

    ALTER PROCEDURE [dbo].[spDb1]
    	@date DATETIME, @ouput int OUTPUT
    AS 
    BEGIN
    
             // Huge amount  lot of logic
    
    END

    For db2Name Sp:

    ALTER PROCEDURE [dbo].[spDb2]
    	@date DATETIME, @output int OUTPUT
    AS 
    BEGIN
            // More logic
    
    END

    I can't provide the current logic :/


    Then I can't help you.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, May 4, 2019 12:52 AM
    Moderator
  • Look carefully at your C# code. The name of the parameter is "@ouput". But in your second SQL procedure it is called "@output". Note the missing "t" in the C# code.

    The other procedure uses @ouput (without the "t") and that's why this one works.

    • Marked as answer by MarSteven Monday, May 6, 2019 3:38 PM
    Sunday, May 5, 2019 8:35 PM
    Moderator
  • Oh yes, that is the error. Thanks for your support :)
    Monday, May 6, 2019 3:39 PM