0

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?

J. Mini
  • 1,868
  • 1
  • 9
  • 38

1 Answers1

3

You can check for @GRADE to be NULL.

CREATE FUNCTION FOO_WITH_GRADES (@GRADE NVARCHAR(30))
RETURNS TABLE
AS RETURN
    SELECT *
    FROM EMPLOYEES
    WHERE GRADE = @GRADE OR @GRADE IS NULL;
Charlieface
  • 52,284
  • 6
  • 19
  • 43
squillman
  • 13,363
  • 3
  • 41
  • 60
  • 1
    Can't set defaults to parameters or make them optional. But the function code is spot on. The calling code would just need to pass NULL as the parameter value. – Sean Lange Aug 25 '22 at 21:13
  • @SeanLange Are you saying that T-SQL doesn't support that `@GRADE NVARCHAR(30) = NULL` syntax? It did seem unfamiliar to me. – J. Mini Aug 25 '22 at 21:14
  • 1
    And, just for reference, user defined T-SQL functions don't support overloading (many built in functions do have optional parameters). – Thom A Aug 25 '22 at 21:14
  • @J.Mini you can do that in a procedure which makes the parameter optional and provides a default value. But you can't do that in a function, either scalar or table valued.. – Sean Lange Aug 25 '22 at 21:15
  • bah, of course... sorry for the confusion. Was stuck on stored procedure param syntax. – squillman Aug 25 '22 at 21:15
  • 2
    @squillman been there myself many times. :D – Sean Lange Aug 25 '22 at 21:15
  • Perfectly logical answer. Just to check, is this good practice? – J. Mini Aug 25 '22 at 21:18
  • Yes this is very common and works well. – Sean Lange Aug 25 '22 at 21:18
  • 1
    Interestingly, you *can* define a default value for a parameter on a function, but that doesn't make it optional. You would have to explicitly state you want the `DEFAULT` value. [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3bec02a61edb934bba0e66975881b15a) – Thom A Aug 25 '22 at 21:19
  • *"Just to check, is this good practice?"* Yes, many people use it, however, I do recommend sticking an `OPTION (RECOMPILE)` in there for such queries; otherwise you *could* end up with some bad query caching. – Thom A Aug 25 '22 at 21:20
  • 1
    @Larnu The clever bit about this is actually that a `NULL` literal gets inlined all the way through, and therefore the left side of the `OR` is completely optimized out. You can see that in action here https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a8db7502316a9759366cff7da49af7bd – Charlieface Aug 26 '22 at 01:24