Stored procedure parameters - Default values

SQL Server stored procedures are good for separating data logic - I like to keep all things SQL together in one resource. I don't like using inline code because it can easily become out-of-date and requries more effort if several applications connect to the database(s). The only problem I had with stored procedures is that I ended up with numerous variations of the same thing (mainly selects). Using default parameters, it is possible to have a single select stored procedure that will always return the same data but the criteria is dynamic.

This example will use the following table:

User ( ID uniqueidentifier, Username nvarchar( 50 ), Password nvarchar( 50 ) )

The stored procedure has 3 parameters; @ID, @Username and @Password mapping to the 3 columns (ID, Username and Password) respectively. The default value of null means that they are optional parameters meaning you can specify none or a combination. Any value(s) passed in will be used in WHERE clause of the SELECT statement. Values of NULL (default) have no effect on the criteria because of the ISNULL check.

Example:

select * [User]
where [ID] = isnull( @ID, [ID] )

If @ID is NULL, the value from the ID column will be used; basically checking if it's equal to itself and returning all of the data inside the table. When the value isn't NULL, only row(s) containing that ID will be returned.


Stored procedure:

create procedure [SelectUser]
 
@ID uniqueidentifier = null
@Username nvarchar( 50 ) = null
@Password nvarchar( 50 ) = null
 
as
 
select * from [User]
where [ID] = isnull( @ID, [ID] ) and [Username] = isnull( @Username, [Username] ) and [Password] = isnull( @Password, [Password] )