Sometimes when I create an API that should enable getting a single value or all values I use the following pattern (passing NULL to the API, means get all rows):
@Usernames – comma separeted list of users
CREATE PROC GetUsers (@Usernames VARCHAR(100) = NULL)
AS
BEGIN
SELECT *
FROM Users
Where @Usernames IS NULL OR dbo.in_list(@Usernames,Username) = 1
END
Is this a good practice to use the OR condition the get both functionalities, or should i write something like this:
CREATE PROC GetUsers (@Usernames VARCHAR(100) = NULL)
AS
BEGIN
IF(@Username IS NULL)
BEGIN
SELECT *
FROM Users
END
ELSE
BEGIN
SELECT *
FROM Users
Where dbo.in_list(@Usernames,Username) = 1
END
END
*Note:
This is only SQL for example, this is not a specific coding language question.
Thanks.
4
Your question title is different from the question within your posting, so I try to answer both questions.
IMHO it is a perfectly valid idiom to have a function with an optional filter condition, and when you leave that filter out, you get the full unfiltered result set. That’s true for SQL as well as for many other programming languages.
As for which implementation is better: your first one is more comprehensive with less repetition of the same code (SELECT * FROM Users) and less boilerplate code (IF .. END ELSE ...) – so in general I would prefer this, since it is clearly better maintainable. Only if you suffer from an unexpected loss of performance you may test if the second alternative is faster. That will probably depend on your database system (maybe on the version), so do this only if you are 100% sure that it will be worth the hussle.
0
Create another procedure named GetEmployeesDetails.
Your procedure is named GetEmployeeDetails, so the procedure should only do one thing: get the details of an employee.
You’re making it do other things than what its name says if it returns something else than the details of one employee.
What’s usually done is having a GetEmployeesDetails taking a filter parameter. This filter can be a hashmap, empty or not.
This is important not to do it your way; your procedure’s name is misleading.
6
In application programming languages, I agree with Doc Brown. Nothing wrong here.
For the SQL implementation, parameter sniffing could create some havoc here. Consult an skilled DBA or read some serious papers on dynamic searching on your db engine of choice for better decision making.