Visual C# stored procedure asking for parameter that isn't needed

Question

Stored procedure:

ALTER PROCEDURE VendorsRowcount
    @RowCount int OUTPUT
AS
    SET NOCOUNT ON

    SELECT * 
    FROM dbo.Vendors

    SET @RowCount = @@ROWCOUNT

    RETURN @RowCount

C#:

using (var conn = new SqlConnection("Data Source=.\\SQLEXPRESS;Initial Catalog=Pricer;Persist Security Info=True;User ID=xxx;Password=xxx"))
using (var command = new SqlCommand("VendorsRowcount", conn)
{
    CommandType = CommandType.StoredProcedure
})
{
    conn.Open();
    command.ExecuteNonQuery();
    conn.Close();
}

I am getting the error:

Additional information: Procedure or function 'VendorsRowcount' expects parameter '@RowCount', which was not supplied.

I am just learning C# after setting out to learn VB and realizing that there are a lot more resources on the internet for C#.

This is probably a stupid question, but I have searched and maybe the terms I use are not the correct ones, because I can not find an answer.

To the best of my knowledge, I don't need to send a parameter because @RowCount is output.

Why do I get this error?

But you have `@RowCount` in your sp? – Soner Gönül Aug 29 '14 at 20:50 — Soner Gönül, Aug 29 '14 at 20:50

Steve · Accepted Answer · 2014-08-30T09:45:37.983

If you declare a parameter in the stored procedure, it has no relevance the fact that is declared as OUTPUT. You need to pass it from your C# code. The alternative is to declare the parameter as optional as shown in another answer. However you have now a problem. How do you read back in your C# code that parameter's value?

First option, pass the parameter at the stored procedure and read it back

conn.Open();
SqlParameter prm = command.Parameters.Add(new SqlParameter("@RowCount", SqlDbType.Int));
prm.Direction = ParameterDirection.Output;
command.ExecuteNonQuery();
Console.WriteLine(prm.Value.ToString());
conn.Close();

Second option, set the parameter as optional, call the SqlCommandBuilder.DeriveParameters method to fill the Parameter collection on the C# side and read it back. (Please read the remarks section in the link provided about the efficiency of this solution)

-- in the stored procedure
@RowCount int = 0 OUTPUT


conn.Open();
SqlCommandBuilder.DeriveParameters(command);
command.ExecuteNonQuery();
Console.WriteLine(command.Parameters["@RowCount"].Value.ToString());
conn.Close();

However I am puzzled by the fact that you run a potentially costly SELECT * command but you don't seems to be interested in the records returned.

In this context, a StoredProcedure seems to be excessive and adds a maintenance issue while you could get the row count simply writing:

conn.Open();
command.CommandText = "SELECT COUNT(*) FROM Vendors";
int rowCount = Convert.ToInt32(command.ExecuteScalar());
Console.WriteLine(rowCount.ToString());
conn.Close();

This worked perfect. And I know my code does/will be bad. I am a network tech, not a programmer. I was just told by my job to also become a programmer, so I'm trying. I have about 3 weeks of experience with VB, and I'm on my first day of messing around with C#. I'm sure theres plenty more stupid questions coming. — Katp00p, Aug 29 '14 at 22:30
This seems as difficult as just typing out the query. I was using stored procedures because I thought they would save time. — Katp00p, Aug 29 '14 at 22:33
Unless really needed for complex operations or to squeeze the last bit of performance I suggest to stay away from SP for just a simple SELECT. For example you could get the COUNT(*) of your records in a simpler way — Steve, Aug 29 '14 at 22:41

score 2 · Answer 2 · edited May 23 '17 at 11:49

2

You need to pass in that parameter. Here's a good example of how to do this:

Get output parameter value in ADO.NET

edited May 23 '17 at 11:49

Community

1
1

answered Aug 29 '14 at 20:55

Dan

5,692
3
35
66

score 1 · Answer 3 · answered Aug 29 '14 at 20:53

1

If the parameter should be optional, you must provide a default value in your Stored Procedure.

For example: @RowCount int OUTPUT = 0

answered Aug 29 '14 at 20:53

Peter M.

1,240
2
9
25

Visual C# stored procedure asking for parameter that isn't needed

3 Answers3