Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Goalie35
Yak Posting Veteran
81 Posts |
 Posted - 2006-02-20 : 12:13:32
|
| This isn't really an issue I'm having. It's more of a curiosity on implementing good coding practices into my stored procedures.Ok, I've inherited a project from the developer who was here before me and is no longer with us and I was just curious about one of his stored procedure development methods that differs from mine and whether or not it's good practice to switch over to his method.In his stored procedures, when declaring the input parameters, he ALWAYS sets them to NULL.For example...-------------------------------------CREATE PROCEDURE sample_Stored_Proc @input_param_1 Int=NULL @input_param_2 Varchar(20)=NULL--------------------------------------I, on the other hand NEVER set them to NULL because in our ASP.NET code, right before we call a stored procedure, we always set an input parameter to NULL if the corresponding variable is empty. My former colleagues' asp.net code was setup the same as well.For example:-----------------------------------------------Dim parameters(0) As SqlParameterparameters(0) = New SqlParameter("@input_param_1", SqlDbType.Int)If sampleVariable <> "" Then parameters(0).Value = sampleVariable Else parameters(0).Value = DBNull.ValueEnd If---------------------------------------------------------This is how I've always setup my input parameters and never had a problem. So my question is actually a two-parter. First, does anyone know why our former colleague setup all of his input parameters this way? Was there any benefit? And 2nd, even though I haven't had a problem yet, am I asking for trouble some day by not setting my parameters to null within the stored procedure, as he did?Thanks in advance.-Goalie35 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-02-20 : 12:30:41
|
| I peronally don't like to default parameters null (unless the logic of the SP requires it). Also I would prefer all the calls use <@param>=<value> rather than a comma seperated list of values just to eliminate ambiguity. However, in the real world with multiple developers and an application life cycle there can be need to modify (add functionality to) procedures but still maintain backward compatibility. That could be a reason to always add new parameters to the end of the list and make them defalut to null.Be One with the OptimizerTG |
 |
|
|
|
|
|
|
|
Stored Proc: Set input parameters to NULL??