Click here to Skip to main content
Click here to Skip to main content

Determining Stored Procedure Parameters with SQL 2005

By , 18 Jun 2006
Rate this:
Please Sign up or sign in to vote.

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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

gtamir
Web Developer
United States United States
Giora Tamir has been Architecting, Designing and Developing software and hardware solutions for over 15 years. As an IEEE Senior member and a talented developer, Giora blends software development, knowledge of protocols, extensive understanding of hardware and profound knowledge of both Unix and Windows based systems to provide a complete solution for both defense and commercial applications. Giora, also known as G.T., now holds the position of Principal Engineer for ProfitLine, Inc. architecting the next generation of .NET applications based on a Service-Oriented-Architecture.
 
Gioras areas of interest include distributed applications, networking and cryptography in addition to Unix internals and embedded programming.
 
Founded in 1992, ProfitLine manages hundreds of millions of dollars in annual telecom spend for its prestigious Fortune 1000 client base, such as Merrill Lynch, Charming Shoppes, Macromedia, CNA Financial Corporation, and Constellation Energy Group. ProfitLine's outsourced solution streamlines telecom administrative functions by combining a best practices approach with intelligent technology. For more information about ProfitLine, call 858.452.6800 or e-mail sales@profitline.com.

Comments and Discussions

 
Generaland in current version of the .net framework? Pinmemberdigimanus20-May-11 0:17 
GeneralBug in SQL Server 2005 Fixed by MS Pinmembercoolpeep4-Jan-07 14:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140415.2 | Last Updated 18 Jun 2006
Article Copyright 2006 by gtamir
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid