I have a stored procedure that has a optional parameter, @UserID VARCHAR(50)
. The thing is, there are two ways to work with it:
- Give it a default value of
NULL
, the have anIF...ELSE
clause, that performs two differentSELECT
queries, one with'WHERE UserID = @UserID'
and without the where. - Give it a default value of
'%'
and then just have the where clause use'WHERE UserID LIKE @UserID'
. In the calling code, the '%' wont be used, so only exact matches will be found.
The question is: Which option is faster? Which option provides better performance as the table grows? Be aware that the UserID
column is a foreign key and is not indexed.
EDIT: Something I want to add, based on some answers: The @UserID
parameter is not (necessarily) the only optional parameter being passed. In some cases there are as many as 4 or 5 optional parameters.