If my ITVF's only argument is used solely as part of a WHERE clause, is there any way to skip t-CodePudding

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?

CodePudding user response：

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;