I recently wrote a view for my users
CREATE VIEW FOO AS SELECT * FROM EMPLOYEES
They used this view to populate an Excel sheet and they were very happy. They later decided that they wanted one Excel sheet per employee grade, but also wanted to keep the original view. This was no big deal and the following code let my users do just what they wanted
CREATE FUNCTION FOO_WITH_GRADES {@GRADE NVARCHAR(30)} AS
SELECT * FROM EMPLOYEES WHERE GRADE = @GRADE
however, for data that didn’t filter by grade, they still needed the original view. I don’t like that. Is there any argument that can be passed to FOO_WITH_GRADES, or any change that I can make to that function, such that my users can get the results of FOO by calling FOO_WITH_GRADES?
>Solution :
You can check for @GRADE to be NULL.
CREATE FUNCTION FOO_WITH_GRADES (@GRADE NVARCHAR(30)) AS
SELECT * FROM EMPLOYEES WHERE GRADE = @GRADE OR @GRADE IS NULL;