Output Parameter not Returned from Stored Proc.

Question

I am calling a SQL proc that has 3 OUTPUT params. After the call to the proc one of the params does not return a value when the other two do. Profiler shows that all 3 values are being returned.

The params are declared as follows in the proc...

@UsrVariableID INT OUTPUT,
@OrganisationName NVARCHAR(256) OUTPUT,
@Visible bit OUTPUT

and the code that calls the proc is like this...

cm.Parameters.AddWithValue("@OrganisationName", name);
cm.Parameters["@OrganisationName"].Direction = ParameterDirection.Output;
cm.Parameters.AddWithValue("@Visible", visible);
cm.Parameters["@Visible"].Direction = ParameterDirection.Output;

cm.ExecuteNonQuery();

name = cm.Parameters["@OrganisationName"].Value.ToString();
visible = bool.Parse(cm.Parameters["@Visible"].Value.ToString());
id = int.Parse(cm.Parameters["@UsrVariableID"].Value.ToString());

The param that fails is @OrganisationName.

I'm wondering if its because the param is of type string in the code but NVARCHAR in the proc.

Anyone got any ideas?

Answer 1

You could try declaring the parameters first (then set the values), and see if this make a difference.

cm.Parameters.Add["@OrganisationName", SqlDbType.NVarChar, 256].Direction = ParameterDirection.Output
cm.Parameters["@OrganisationName"].Value = name

But to me there doesn't look like anything wrong with what you have posted.

Incidently, if you shouldn't need the .Parse(.ToString()) you should only need to cast.

visible = bool.Parse(cm.Parameters["@Visible"].Value.ToString());

becomes

visible = (bool)cm.Parameters["@Visible"].Value;

Answer 2

I'm not 100% sure about MS SQL, but in .NET-->Oracle you need to specify the string buffer size.

cm.Parameters["@OrganisationName"].Size = 256;

Answer 3

With output parameters that have variable length data types (nvarchar, varchar, etc), I've found that being more explicit leads to better results. In the case you've posted, a type is not specified on the C# side. I would probably change things to look something like the following:

SqlParameter theOrganizationNameParam = new SqlParameter( "@OrganisationName", SqlDbType.NVarChar, 256 );
theOrganizationNameParam.Direction = ParameterDirection.Output;
cm.Parameters.Add( theOrganizationNameParam );
cm.ExecuteNonQuery();
name = theOrganizationNameParam.Value;

With this you can guarantee the output paratmer has the correct data type, and therefore can access the Value property without and exception being thrown.

Hope this sheds some light.

Answer 4

Thanks everyone, As suggested by Ady and Scott explicly declaring the type in the parameter declaration solved it.I picked Scotts answer as it was more concise.

Answer 5

Use ParameterDirection.InputOutput instead - this works

See last comment in here for more info - http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic28087.aspx

Answer 6

Actually, it's not about declaring the type.

For output parameters with varying size, it is necessary to specify the parameter size.

cm.Parameters["@OrganisationName"].Size = 50;

I've read somewhere there was a bug in implementation that raises an exception whenever the size is not specified for certain data types.

The whole thing makes it badly suitable for returning parameters with unknown size, like nvarchar(max). I'd recommend return values via SELECT rather that via output parameters.

Answer 7

Yes, i tried here a lot of things and the only thing that works is: you need to specify the SIZE of string.

I have a storedprocedure that returns a string nvarchar(7) and at the C#.NET side i specified the size=255 and this is working for me to receive a string of 7 chars....

I didn't like this because the code should be more expert...anyways...