I was thinking today about if I would be able to discover if a Stored
Proc had any Parameters and if it did would I be able to discover the
parameter types. The Answer is YES, you can quite easily
All you need is this little stored proc
CREATE Proc sp_Get_sproc_params( @SprocName varchar(100))
as
Select sc.name as 'ParameterName' , st.name as 'Type' from syscolumns sc
inner join systypes st on sc.xtype = st.xtype
where id = object_id(@SprocName)
If you feed in the stored Proc name is will Return the parameter
types i.e If you run the followingh SQL statement after creating
the Stored Proc
sp_Get_sproc_params 'sp_Get_sproc_params'
You will get the Below result
@SprocName varchar
What would you want to do with this? Well this is the next part of the
adventure I was going down today. I could now dynamically call a
stored proc with an XML document and now could assign the Parameter
values as what I have in my XML document. fields
i.e
public static int ExecuteScalar(string Function , XmlDataDocument xmlDoc)
{
string sProcName = GetProcName(Function);
if(sProcName == null || sProcName == string.Empty) throw new
ArgumentNullException( "connectionString" );
using(SqlCommand oComm = GetCommand())
{
oComm.CommandText = sProcName;
using(SqlDataReader oParamters = GetParameters(sProcName))
{
if(oParamters == null) throw new ArgumentNullException("Parameters");
while (oParamters.Read())
{
string sParamName = oParamters["ParameterName"].ToString();
foreach(XmlNode oNode in xmlDoc.DocumentElement.ChildNodes)
{
if(oNode.Name.ToLower() == sParamName.Substring(1).ToLower())
{
oComm.Parameters.Add(sParamName,oNode.InnerText);
}
}
}
if(!oParamters.IsClosed){oParamters.Close();}
}
if(oComm.Connection.State ==
ConnectionState.Closed){oComm.Connection.Open();}
return Convert.ToInt32(oComm.ExecuteScalar());
}
}
private static SqlDataReader GetParameters(string FunctionName)
{
try
{
using(SqlCommand oComm = GetCommand())
{
if(oComm != null)
{
oComm.Connection.Open();
oComm.CommandText = "sp_Get_SPROC_Params";
oComm.Parameters.Add( "@SprocName",FunctionName);
return oComm.ExecuteReader(CommandBehavior.CloseConnection);
}
else
{
return null;
}
}
}
}
Although In the above example I have not added the SQL Parameters as an
Explicit type one could do this with not much more effort.
Why would you want to do this, the answer is well why not :-)
We were discussing in the office today that wouldn't it be neat if we
could do something like this, and it was mentioned that the
sqlCommandBuilder class did have some hang ups apparently , I don't
know of this as to be honest I have never attempted to use that class
due to the fact of pure laziness