1 stored procedure with flexible parameters, or many stored procedures?

Question

Hi,

I'd like to get your opinion on 2 ways of implementing the same stored procedure. Any advice would be greatly appreciated.

Implementation 1

CREATE PROC GetFileSize(@Path varchar(500) = NULL, @FileID int = NULL)
AS 
     IF @Path IS NULL 
         ' Find the file by @FileID and return its size
     ELSE
         ' Find the file by @Path and return its size

Implementation 2

CREATE PROC GetFileSizeByPath(@Path varchar(500))
AS

CREATE PROC GetFileSizeByFileID(@FileID int)
AS

Which implementation do you prefer and why?

Cheers, Mosh

Answer 1

The second one is more elegant and clean.

The only reason to do the first one is if the code for both methods is nearly identical. Then it would be justified to prevent code duplication. In all other scenarios I dont see why you would mix those as taking this approach to the extreme would be one huge stored procedure that has one large if statement to decide which code to execute given some specific parameters, which totally defeats the purpose of modular design.

Answer 2

I prefer a single stored proc with optional parameters as this often means code does not have to be duplicated over a number of procs. The use of a default value of NULL is a very powerful facility. I often use something like:

SELECT * FROM Table WHERE ISNULL(@Parameter, Value) = Value

If the parameter is NULL it will match all records, otherwise only those requested.

Answer 3

If there are just two variants (as in your example), and they never overlap, then separate procs can be easier to find (and the names are more descriptive).

If it's a generalised search for e.g. people, where there might be 10 different parameters, some or all of which may be provided, in varying combinations, then it would make more sense to create a single procedure that deals with these variations.

For generalised search in SQL, I'd always recommend Erland Sommarskog's articles