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

Question

_{Friday, May 3, 2019 10:20 PM}

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?

All replies (5)

_{Sunday, May 5, 2019 8:35 PM ✅Answered | 1 vote}

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.

_{Friday, May 3, 2019 11:23 PM}

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

_{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 :/

_{Saturday, May 4, 2019 12:52 AM}

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

_{Monday, May 6, 2019 3:39 PM}

Oh yes, that is the error. Thanks for your support :)