Passing output parameters to SQL Server stored procedures through an SqlCommand object's Parameters

[Mark Rae]

Hi,

I'm currently working writing a Windows service in C# which needs, among
other things, to interface with Microsoft Great Plains 8.0, specifically to
add General Ledger entries. In order to add a new General Ledger entry to a
Great Plains database, it's necessary to supply a journal entry number -
Microsoft provide a stored procedure which calculates the most recent
journal entry number used, increments this value by one, and returns this to
the caller in the form of an output parameter. The top of the procedure is
as follows:


CREATE PROCEDURE dbo.glGetNextJournalEntry
@l_tINCheckWORKFiles tinyint = NULL,
@I_iSQLSessionID int = NULL,
@IO_iOUTJournalEntry int = NULL output,
@O_tOUTOK tinyint = NULL output,
@O_iErrorState int = NULL output
AS
IF((@l_tINCheckWORKFiles IS NULL) OR (@IO_iOUTJournalEntry IS NULL) OR
(@I_iSQLSessionID IS NULL))
BEGIN
SELECT
@O_iErrorState = 20159,
@IO_iOUTJournalEntry = 0,
@O_tOUTOK = 0
RETURN
END

<snipped rest of proc>

@IO_iOUTJournalEntry returns the next journal entry number
@O_tOUTOK returns 0 or 1 depending on whether the procedure succeeds or
fails
@O_iErrorState contains 0 if the procedure succeeds, or the error state if
it fails


I'm calling the procedure as follows:

SqlConnection mobjSqlConn;
SqlCommand mobjSqlCommand;
SqlParameter mobjSqlParameter;
ArrayList maobjSqlParameters = new ArrayList();
SqlDataReader mobjDR = null;

maobjSqlParameters.Clear();

mobjSqlParameter = new SqlParameter("@l_tINCheckWORKFiles",
SqlDbType.TinyInt);
mobjSqlParameter.IsNullable = true;
mobjSqlParameter.Value = 0;
maobjSqlParameters.Add(mobjSqlParameter);

mobjSqlParameter = new SqlParameter("@I_iSQLSessionID", SqlDbType.Int);
mobjSqlParameter.IsNullable = true;
mobjSqlParameter.Value = 0;
maobjSqlParameters.Add(mobjSqlParameter);

mobjSqlParameter = new SqlParameter("@IO_iOUTJournalEntry", SqlDbType.Int);
mobjSqlParameter.IsNullable = true;
mobjSqlParameter.Direction = ParameterDirection.Output;
mobjSqlParameter.Value = 0;
maobjSqlParameters.Add(mobjSqlParameter);

mobjSqlParameter = new SqlParameter("@O_tOUTOK", SqlDbType.TinyInt);
mobjSqlParameter.IsNullable = true;
mobjSqlParameter.Direction = ParameterDirection.Output;
mobjSqlParameter.Value = 0;
maobjSqlParameters.Add(mobjSqlParameter);

mobjSqlParameter = new SqlParameter("@O_iErrorState", SqlDbType.Int);
mobjSqlParameter.IsNullable = true;
mobjSqlParameter.Direction = ParameterDirection.Output;
mobjSqlParameter.Value = 0;
maobjSqlParameters.Add(mobjSqlParameter);

mobjSqlConn = new SqlConnection(CGlobal.gstrSQLConnectionString);
mobjSqlCommand = new SqlCommand("glGetNextJournalEntry", mobjSqlConn);
mobjSqlCommand.CommandType = CommandType.StoredProcedure;
foreach(SqlParameter objSqlParameter in maobjSqlParameters)
{
mobjSqlCommand.Parameters.Add(objSqlParameter);
}
mobjSqlCommand.Connection.Open();
mobjDR = mobjSqlCommand.ExecuteReader(CommandBehavior.CloseConnection);


It all works as far as here, i.e. there are no errors and mobjDR contains an
SqlDataReader, obviously with no records. However, the procedure does not
return the next journal entry number. The values of the three output
parameters are as follows:

@IO_iOUTJournalEntry - 0
@O_tOUTOK - 0
@O_iErrorState - 20159

Clearly, the procedure is not getting past the first validation (see above).
So I enabled SQL debugging in the probject and set a breakpoint at the top
of the procedure - turns out that all three output parameters have lost
their initial values by the time they get passed into the procedure i.e.
they are all being passed in as NULL.


Therefore, I tried to send a hardcoded SQL string to the procedure instead
i.e.

string strSQL = "";
strSQL += "DECLARE @IO_iOUTJournalEntry int, @O_tOUTOK tinyint,
@O_iErrorState int ";
strSQL += "SELECT @IO_iOUTJournalEntry = 0, @O_tOUTOK = 0, @O_iErrorState =
0 ";
strSQL += "EXEC glGetNextJournalEntry 0, 0, @IO_iOUTJournalEntry out,
@O_tOUTOK out, @O_iErrorState out ";
strSQL += "SELECT @IO_iOUTJournalEntry";
mobjSqlConn = new SqlConnection(pstrSqlConnectionString);
mobjSqlCommand = new SqlCommand(pstrSQL, mobjSqlConn);
mobjSqlCommand.Connection.Open();
mobjDR = mobjSqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
mobjDR.Read();
intNextJournalEntry = mobjDR.GetInt32(0);
mobjDR.Close();


This worked perfectly.

Can anyone please tell me what I'm doing wrong? Is there something specific
about passing output parameters to SQL Server stored procedures in the
SqlCommand's Parameters collection which makes them lose their initial
values?

Any assistance gratefully received.

Regards,

Mark Rae

 

[Cor Ligthert [MVP]]

Mark,

I don't see you anywhere fill the values of your parametercollection. Do I
miss something in your code. (Moreover I see you set them all initial to
zero).

Cor

 

[Mark Rae]

I don't see you anywhere fill the values of your parametercollection.

mobjSqlParameter.Value = 0;

Moreover I see you set them all initial to zero.

Correct - they have to be...

 

[Norman Yuan]

Why you use Command.ExcecuteReader(), if you have deifned output parameter?

Simply

maobjSqlCommand.ExecuteNonQuery()
int err=(int)maobjSqlCommand.Parameters["@IO_iOUTJournalEntry "].Value;
....

 

[Mark Rae]

Why you use Command.ExcecuteReader(), if you have deifned output
parameter?

Why not?

Simply

maobjSqlCommand.ExecuteNonQuery()
int err=(int)maobjSqlCommand.Parameters["@IO_iOUTJournalEntry "].Value;

Makes no difference - the values of the output parameters still get turned
into NULL when they reach the SP...

 

[Norman Yuan]

After second look at your code, I think your problem is the in the following
code block:

mobjSqlParameter = new SqlParameter("@IO_iOUTJournalEntry", SqlDbType.Int);
mobjSqlParameter.IsNullable = true;
mobjSqlParameter.Direction = ParameterDirection.Output;
mobjSqlParameter.Value = 0;
maobjSqlParameters.Add(mobjSqlParameter);

Here, you defined parameter "@IO_iOUTJournalEntry" as OUTPUT parameter,
while your SP starts with an "IF..." statement, where you use
"@IO_iOUTJournalEntry" in the condition.

Since it is defined as OUTPUT parameter, even you assign its value to 0, the
value will not get passed into the SP. (Your debugging has proved that NULL
was passed in, right?)

So, you either remove "@IO_iOOUTJournalEntry" from the SP's "IF..."
condition (I do not see the reason why you put it there), or change the
paramter direction type in your code to ParameterDirection.InputOutput, so
that value 0 can be passed in:

mobjSqlParameter.Direction = ParameterDirection.InputOutput;
mobjSqlParameter.Value = 0;

As for using ExecuteReader() or ExecuteNonQuery(), yes, you can get what you
want. There could be little difference if:

reader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
reader.Close();
//Then get returned value from Paramters collection

Even you do not call DataReader.Read() here, a DataReader object is created
in memory, for doing nothing.

However, if you:

reader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
reader.Read(); //Assume your SP's last line of code is like "SELECT
@para1,@Para2..."
//Get returned value
reader.Close();

Then, you retrieve the same set of returned data twice from SQL Server: one
set returned by Command's Parameters collection and one set from datareader
when you call DataReader.Read(). That is what I'd ask why.

Why you use Command.ExcecuteReader(), if you have deifned output
parameter?

Why not?

Simply

maobjSqlCommand.ExecuteNonQuery()
int err=(int)maobjSqlCommand.Parameters["@IO_iOUTJournalEntry "].Value;

Makes no difference - the values of the output parameters still get turned
into NULL when they reach the SP...

 

[Mark Rae]

Here, you defined parameter "@IO_iOUTJournalEntry" as OUTPUT parameter,

That's correct.

while your SP starts with an "IF..." statement, where you use
"@IO_iOUTJournalEntry" in the condition.

Please re-read my original post. It's not *my* stored procedure - it's
Microsoft's stored procedure, part of their Great Plains product. I'm just
trying to use it.

Since it is defined as OUTPUT parameter, even you assign its value to 0,
the value will not get passed into the SP. (Your debugging has proved that
NULL was passed in, right?)

That was the point of my original post. Why is this? Is this behaviour by
design? Is it a bug in the Parameters collection object?

So, you either remove "@IO_iOOUTJournalEntry" from the SP's "IF..."
condition (I do not see the reason why you put it there), or change the
paramter direction type in your code to ParameterDirection.InputOutput, so
that value 0 can be passed in:

Please see above - I cannot change the stored procedure, as that would
invalidate the license / warranty / support agreement etc... This just isn't
an option.

As for using ExecuteReader() or ExecuteNonQuery(), yes, you can get what
you want. There could be little difference if:

Indeed, and that's not really the point of what I was asking.

To summarise, why are values converted to NULL when passed to stored
procedures through an SqlCommand object's Parameters collection, but not
when passed in via dynamic SQL?

 

[Norman Yuan]

OK, since you cannot change the SP, only thing you need to do is

mobjSqlParameter.Direction = ParameterDirection.InputOutput;

Note: the Parameter direction is InputOutput, because you must pass a
non-null value to the SP AND read return value in it. Do not confuse the
parameter in SP, which is defined as OUTPUT, and the SqlClient.Parameter
here, which you can define it either as Output or InputOutput. In your case,
it must be an InputOutput and has a non-null value in order for the SP to
work as you expected.

 

[Mark Rae]

mobjSqlParameter.Direction = ParameterDirection.InputOutput;

Ah - perfect! Thanks very much.