How can I write a SQL stored procedure where I want the parameters to be optional in the select statement?
1
-
What database system, and which version?? SQL is just the Structured Query Language - a language used by many database systems, but not a a database product... details about the features of stored procedures etc. are often vendor-specific - so we really need to know what database system you're using....marc_s– marc_s2012-07-22 19:53:02 +00:00Commented Jul 22, 2012 at 19:53
Add a comment
|
1 Answer
try this.. Make the SPs input parameters that control the filtering optional, witrh default values of null. In each select statement's Where clause, write the predicate like this:
Create procedure MyProcedure
@columnNameValue [datatype] = null
As
Select [stuff....]
From table
Where ColumnName = Coalesce(@columnNameValue , ColumnName)
this way if you do not include the parameter, or if you pass a null value for the parameter, the select statement will filter on where the column value is equal to itself, (effectively doing no filtering at all on that column.)
The only negative to this is that it prevents you from being able to pass a null as a meaningfull value to explicitly filter on only the nulls.... (i.e., Select only the rows where the value is null) Once the above technique has been adopted, you would need to add another parameter to implement that type of requirement. ( say, @GetOnlyNulls TinyInt = 0, or something similar)
Create procedure MyProcedure
@columnNameValue [datatype] = null,
@GetOnlyNulls Tinyint = 0
As
Select [stuff....]
From table
Where (ColumnName Is Null And @GetOnlyNulls = 1)
Or ColumnName = Coalesce(@columnNameValue , ColumnName)
1 Comment
CodeLikeBeaker
I like this. Never thought of doing it like this. +1