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, Aug 03 '10 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, Aug 03 '10 at 14:31

JonH · Answer 1 · 2010-08-03T14:37:43.043

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 03 '10 at 14:37

answered Aug 03 '10 at 14:21

JonH

32,732
12
87
145

I don't want input parameters because then the values aren't getting returned from the SQL server to the client. – Rachel Aug 03 '10 at 14:30
1

@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 Aug 03 '10 at 14:31
@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. – JonH Aug 03 '10 at 14:46

Jon Skeet · Accepted Answer · 2010-08-03T14:33:50.117

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 03 '10 at 14:33

answered Aug 03 '10 at 14:21

Jon Skeet

1,421,763
867
9,128
9,194

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. – Rachel Aug 03 '10 at 14:23
2

@Rachel - You will need to post the stored procedure as well. – JonH Aug 03 '10 at 14:25
3

@Rachel, you can use the same parameter for both, but then the direction is InputOutput, not Output. – Dave D Aug 03 '10 at 14:30
1

@Rachel: See my edit. If you want to use it for input and output, say so! – Jon Skeet Aug 03 '10 at 14:34
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. – Rachel Aug 03 '10 at 14:42
@Jon Skeet - +1 - Your eye caught it before mine did. – JonH Aug 03 '10 at 14:47

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

2 Answers2