Determining Stored Procedure Parameters with SQL 2005

Introduction

Have you tried using SQL Server 2005 with your .NET 1.1 application? If your application uses the Microsoft Data Access Application Block or calls SqlCommandBuilder.DeriveParameters(), you have been encountering weird errors.

The problem exists because the SqlCommandBuilder.DeriveParameters() method does not pass the schema name parameter (@procedure_schema) to the sp_procedure_params_rowset. In SQL Server 2000, the schema name is almost always “dbo”, so it is sufficient to pass in just the stored procedure name.

The same problem can be seen when attempting to access the database using the Visual Studio 2003 server explorer. The server explorer will connect to the database but will not show stored procedure parameters correctly.

The problem is compounded by the fact that SQL Server 2005 supports the new ‘XML’ data type – a type that does not exist in the SqlDbType enumeration in 1.1.

The Approach

Encode the schema name as part of your store procedure name. I use the simple method of prefixing the SP name with the schema. If the SP name is “OM_Get_Order_XML” and the schema is “OM_Order”, the encoded SP name would be “OM_Order.OM_Get_Order_XML”.

You can use the following code snippet to decode the stored procedure name:

string schemaName = "dbo";
int firstDot = spName.IndexOf('.');
if (firstDot > 0) 
{
    schemaName = spName.Substring(0, firstDot);
    spName = spName.Substring(firstDot+1);
}

Note that the above call falls back to the old “dbo” schema if the schema name is not specified.

Now that we have both the schema name and the stored procedure name, we can manually determine the stored procedure parameters.

Determining Stored Procedure Parameters

I run my code in a mixed SQL Server 2000 & 2005 environment. I therefore chose to write my code for maximum compatibility. I first try to run the SqlCommandBuilder.DeriveParameters() method, and if the method fails – try the manual approach.
The code is:

private static SqlParameter[] DiscoverSpParameterSet
	(SqlConnection connection, string spName, bool includeReturnValueParameter)
{
if( connection == null ) throw new ArgumentNullException( "connection" );
if( spName == null || spName.Length == 0 ) throw new ArgumentNullException( "spName" );

            // Original cmd object
SqlCommand cmd = new SqlCommand(spName, connection);
cmd.CommandType = CommandType.StoredProcedure;

// Hack to check for schema name in the spName
string schemaName = "dbo";
int firstDot = spName.IndexOf('.');
if (firstDot > 0) 
{
    schemaName = spName.Substring(0, firstDot);
    spName = spName.Substring(firstDot+1);
}

connection.Open();            

// Now this is not neat - I am trying the SQL2000 version and if it fails
// I go to the manual SQL2005 version
try 
{
    // First, attempt the SQL2000 version (no schema)
    SqlCommandBuilder.DeriveParameters(cmd);
}
catch 
{
    // If we are here, SQL2000 call failed
    // Manually run the 'derive params' SP
    // this time with the schema name parameter
    SqlCommand getParams = new SqlCommand("sp_procedure_params_rowset", connection);
    getParams.CommandType = CommandType.StoredProcedure;
    getParams.Parameters.Add("@procedure_name", spName);
    getParams.Parameters.Add("@procedure_schema", schemaName);
    SqlDataReader sdr = getParams.ExecuteReader();
            
    // Do we have any rows?
    if (sdr.HasRows) 
    {
        using (sdr) 
        {
            // Read the parameter information
            int ParamNameCol = sdr.GetOrdinal("PARAMETER_NAME");
            int ParamSizeCol = sdr.GetOrdinal("CHARACTER_MAXIMUM_LENGTH");
            int ParamTypeCol = sdr.GetOrdinal("TYPE_NAME");
            int ParamNullCol = sdr.GetOrdinal("IS_NULLABLE");
            int ParamPrecCol = sdr.GetOrdinal("NUMERIC_PRECISION");
            int ParamDirCol = sdr.GetOrdinal("PARAMETER_TYPE");
            int ParamScaleCol = sdr.GetOrdinal("NUMERIC_SCALE");

            // Loop through and read the rows
            while (sdr.Read()) 
            {
                string name = sdr.GetString(ParamNameCol);
                string datatype = sdr.GetString(ParamTypeCol);
                // Is this xml?
                // ADO.NET 1.1 does not support XML, replace with text
                if (0 == String.Compare("xml", datatype, true)) 
                {
                    datatype = "Text";
                }
                object parsedType = Enum.Parse(typeof(SqlDbType), datatype, true);
                SqlDbType type = (SqlDbType)parsedType;
                bool Nullable = sdr.GetBoolean(ParamNullCol);
                SqlParameter param = new SqlParameter(name,type);
                // Determine parameter direction
                int dir = sdr.GetInt16(ParamDirCol);
                switch (dir)
                {
                    case 1:
                        param.Direction = ParameterDirection.Input;
                        break;
                    case 2:
                        param.Direction = ParameterDirection.Output;
                        break;
                    case 3:
                        param.Direction = ParameterDirection.InputOutput;
                        break;
                    case 4:
                        param.Direction = ParameterDirection.ReturnValue;
                        break;
                }
                param.IsNullable = Nullable;
                if (!sdr.IsDBNull(ParamPrecCol))
                {
                    param.Precision = (Byte)sdr.GetInt16(ParamPrecCol);
                }
                if (!sdr.IsDBNull(ParamSizeCol)) 
                {
                    param.Size = sdr.GetInt32(ParamSizeCol);
                }
                if (!sdr.IsDBNull(ParamScaleCol))
                {
                    param.Scale = (Byte)sdr.GetInt16(ParamScaleCol);
                }
                    cmd.Parameters.Add(param);
            }
        }
    }
}
finally
{
        connection.Close();
}

if (!includeReturnValueParameter) 
{
    cmd.Parameters.RemoveAt(0);
}
                
SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];

cmd.Parameters.CopyTo(discoveredParameters, 0);

// WORKAROUND begin
foreach (SqlParameter sqlParam in discoveredParameters)
{
    if ((sqlParam.SqlDbType == SqlDbType.VarChar) &&
        (sqlParam.Size == Int32.MaxValue))
    {
        sqlParam.SqlDbType = SqlDbType.Text;
    }
}
// WORKAROUND end

// Init the parameters with a DBNull value
foreach (SqlParameter discoveredParameter in discoveredParameters)
{
    discoveredParameter.Value = DBNull.Value;
}
return discoveredParameters;
}

Note to Data Access Application Block Users

The code above is a complete replacement for the DiscoverSpParameterSet(SqlConnection connection, string spName, bool includeReturnValueParameter) method of the SqlHelperParameterCache class.

History

• Version 1.0 – released 10-June-2006