C# - Output SqlParameter uses different values then the ones given?

Question

I have a SqlCommand which runs a stored procedure that contains two integer output parameters. Right before the SqlCommand runs I can see that the output parameters are set to the correct values, however when the command actually executes, it uses a NULL for parameter1 and 0 for parameter2 (verified using SQL Profiler) regardless of what I set the parameters to.

A simplified version of the code is this:

foreach (KeyValuePair<string, object> parameter in outputParameters)
{
    SqlParameter param = new SqlParameter(parameter.Key, parameter.Value);
    param.Direction = ParameterDirection.Output;
    command.Parameters.Add(param);
}

command.ExecuteNonQuery();

I'm confused by two different things here:

1) Why isn't it using the values in the parameters? I can place a breakpoint right before command.ExecuteNonQuery() and see that the command.Parameters list has the output parameters set correctly, however the SQL profiler trace has different parameters when the query gets executed.

2) Both parameters are integers and defined the exact same way - Why is one set to NULL while the other set to 0??

Stored proc is a bit long and shouldn't be the problem... It runs fine from within SQL Management Studio. My problem is the values being passed from the SQLCommand to SQLServer are not what they say they should be. — Rachel
– Rachel, Commented Aug 3, 2010 at 14:26
Your problem is that We are unable to see whether your parameters should be input, output or InputOutput. Without seeing what you are trying to do with them, no-one can provide the right answer. Don't worry about making the question very big. I have the bandwidth to handle it. — Daniel Dyson
– Daniel Dyson, Commented Aug 3, 2010 at 14:31

JonH · Accepted Answer · 2010-08-03 14:37:43Z

4

You want to change the direction to InputOutput (per op edit) rather than output.

When you reference output parameters you are telling the code that the values should return from the actual stored procedure rather then from your code. Even if your code contains a value, your code actually doesn't care what those values are as you specified output parameters.

Here's what it should be:

foreach (KeyValuePair<string, object> parameter in outputParameters)
{
    SqlParameter param = new SqlParameter(parameter.Key, parameter.Value);
    param.Direction = ParameterDirection.InputOutput;
    command.Parameters.Add(param);
}

command.ExecuteNonQuery();

edited Aug 3, 2010 at 14:37

answered Aug 3, 2010 at 14:21

JonH

33.2k13 gold badges93 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rachel Over a year ago

I don't want input parameters because then the values aren't getting returned from the SQL server to the client.

JonH Over a year ago

@Rachel - Then use InputOutput as @Jon Skeet has recommended. But your original post indicated Output only and that does not work like that. Output only indicates that the value should come from your db layer (sproc in this case).

JonH Over a year ago

@Rachel, In reality Skeet realized InputOutput faster then I did so his answer should of been accepted. I only noticed it after your comments on here.

Jon Skeet · Accepted Answer · 2010-08-03 14:33:50Z

3

They're output parameters - i.e. the value is meant to come from the stored procedure... logically they don't have values on input. If you want the values from the client to be used in the stored procedure, they should have a direction of InputOutput.

EDIT: Responding to your comment - Output means just output. Input means just input. InputOutput means both ways. It sounds like you want both ways, so use InputOutput.

As for why one is null and the other not... I don't know, to be honest.

edited Aug 3, 2010 at 14:33

answered Aug 3, 2010 at 14:21

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

6 Comments

Rachel Over a year ago

You can use the same parameter for both input and output. I can run the procedure with no problems from SQL management studio using valid parameters. The reason is the value is used in the query, although there's a chance it will be modified and I want that change to be returned to the client.

JonH Over a year ago

@Rachel - You will need to post the stored procedure as well.

Dave D Over a year ago

@Rachel, you can use the same parameter for both, but then the direction is InputOutput, not Output.

Jon Skeet Over a year ago

@Rachel: See my edit. If you want to use it for input and output, say so!

Rachel Over a year ago

Thank you! lol I thought there was just Input and Output, like SQL. No wonder I was so confused. Sorry I didn't say so sooner, I tried simplifying the problem down to what I thought it was and eliminate the extra details.

|

Collectives™ on Stack Overflow

C# - Output SqlParameter uses different values then the ones given?

2 Answers 2

3 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related