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.
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.
create procedure [SelectUser]
@ID uniqueidentifier = null
@Username nvarchar( 50 ) = null
@Password nvarchar( 50 ) = null
select * from [User]
where [ID] = isnull( @ID, [ID] ) and [Username] = isnull( @Username, [Username] ) and [Password] = isnull( @Password, [Password] )