WARNING: This is a technical entry with no business related information. It relates to Microsoft SQL Server, SubSonic and .NET with code written in C#.
I was responding to an issue in the SubSonic forums a few weeks back when I discovered that the INFORMATION_SCHEMA.PARAMETERS view didn't contain information on the nullability of a stored procedure's parameters. No problem, I did a little searching and found the following two stored procedures: sp_sproc_columns and sp_procedure_params_rowset. Excited at the promise of valuable information on the nullability of my parameters, I ran the following script to create a quick stored procedure that contained both a parameter with a default and one without.
CREATE PROC TestProc (@intDefault int = 0, @intNoDefault int)
AS
SELECT @intDefault as 'ValueEntered'
I ran both of the stored procedures above only to find that the nullability for both parameters was the same: nullable. Hmm. Ok. I must be misunderstanding what it means for a parameter to be nullable. At this point, I began to see that I had confused having a default value, which determines if having at least some value (maybe NULL) is required, and being nullable. So I dug into SQL Books Online and found that parameters are nullable by default. So in SQL Server you can have a parameter that's required, but nullable. That is, the parameter is required, but you can pass in NULL as a value for the parameter.
That said, let's jump over to SubSonic, my favorite ORM of late, which, by the way, I'm really hoping continues to maintain a critical mass of developer interest even as Microsoft is working hard to release LINQ to SQL. I think Rob, Eric, et. al. have done a great job balancing simplicity and ease of use with critical features. There's a lot behind SubSonic, but it's also easy to use, an extremely important balance that's needed for any technology to survive.
One of the things I absolutely love about SubSonic is the fact that it supports the strongly typed use of all of my stored procedures. I wish there was a named indexor for the parameters collection, but that may be a good topic for another post. The issue at hand with SubSonic's implementation of the stored procedure parameters is that it uses nullable types for all of the parameters regardless of whether there is a default value assigned. Phil Haack mentions in this issue on CodePlex that it would be great if SubSonic could distinguish between parameters that have default values and those that don't and are therefore required parameters.
Having searched high and low through the meta data available to mere mortals like me in SQL Server, and having seen a post in the newsgroups from someone who said that the only way to determine if a stored procedure parameter had a default value was to parse the TSQL, I decided to write a routine to do just this using regular expressions.
The routine is available inside the patch download in the SubSonic Issues log on CodePlex. I've also included code you can use to test retrieving the default value status of each of a stored procedure's parameters. To use the following code, create a web form with two textboxes, two labels and a button as follows: txtDatabase, txtSproc, lblSproc, lblArguments and btnProcessSproc. In the click event of the button, place the following code (Written in C#):
string cnString = @"Data Source=Server;Initial Catalog=" + txtDatabase.Text +
";User ID=sa;Password=password;";
string SQL = "EXEC sp_helptext " + txtSproc.Text;
SqlDataReader sprocReader;
StringBuilder sprocBuilder = new StringBuilder();
string sproc = string.Empty;
string arguments = string.Empty;
string[] argumentArray = new string[0];
Hashtable parameterNullibility = new Hashtable();
using (SqlConnection cn = new SqlConnection(cnString))
{
cn.Open();
SqlCommand cmd = new SqlCommand(SQL, cn);
sprocReader = cmd.ExecuteReader();
while (sprocReader.Read())
{
sprocBuilder.Append(sprocReader.GetString(0));
}
sproc = sprocBuilder.ToString();
}
//Replace Newlines with spaces
sproc = Regex.Replace(sproc, @"\r\n", " ");
//Replace multiple white spaces with just one space, also replace tab characters with single space
sproc = Regex.Replace(sproc, @"\s+", " ");
//Remove extra spaces around commas just to be safe
sproc = Regex.Replace(sproc, @"\s*,\s*", ",");
//Remove any comments
sproc = Regex.Replace(sproc, @"/\*.*?\*/", "");
lblSproc.Text = sproc;
string regex = @"CREATE\s+((PROC)|(PROCEDURE))\s+((\[.+?\]\.?){*}|(\S*))(?<Params>.*?)\s{1}as\s{1}";
RegexOptions options = RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture;
MatchCollection matches = Regex.Matches(sproc, regex, options);
if (matches.Count > 0 && matches[0].Groups["Params"] != null)
{
Match match = matches[0];
arguments = match.Groups["Params"].ToString();
argumentArray = Regex.Split(arguments, ",");
foreach (string sArgument in argumentArray)
{
//first retrieve the name of the parameter using Regex
MatchCollection paramMatches = Regex.Matches(sArgument, @"(?<ParamName>@\S*)", options);
MatchCollection nullMatches = Regex.Matches(sArgument, @"(?<NullString>=\s*)", options);
if (paramMatches.Count > 0 && paramMatches[0].Groups["ParamName"] != null)
{
bool isNullDefault = (nullMatches.Count > 0 && nullMatches[0].Groups["NullString"] != null);
parameterNullibility.Add(paramMatches[0].Groups["ParamName"].ToString(), isNullDefault);
}
}
}
foreach (string sParam in parameterNullibility.Keys)
{
string nullability = ((bool)parameterNullibility[sParam]) ? "Default value is set" : "No Default.";
lblArguments.Text += sParam + ": " + nullability + "<br />";
}
Change the connection string information and run the page to view the information about the defaults. You may also want to change the names of the variables which reflect my initial focus on nullability before I realized that the broader issue was whether a parameter had a default value.
This code could be extended to retrieve not just a boolean value determining whether a parameter contains a default value, but also the default value itself.
If you find this useful or have any questions, let me know!
I forgot to mention that I tried to test this against as many formatting scenarios as possible. If you find a stored procedure that doesn't work with the script, please let me know by posting here in the comments!