Is it possible in SQL Server to define a user defined function with fixed enumerable parameters?
Like many pre-defined functions in SQL Server like DATEDIFF that takes DAY, MONTH, etc as first parameter, but these are not char, or any other data types...
I think that it should be easy to find the answer on Internet, but I don't know what I should exactly search. 😅😅
SQL Server doesn't have constants or enums in that sense; parameters to functions or procedures require to pass in strings, numbers, or variables.
Yes, this is unlike the ability to use well-defined constants directly in built-in functions and other T-SQL structs. Maybe that's something we'll see in a future evolution of the language, but this is what we have today.
For things like DATEADD you are passing an identifier... note that these work:
SELECT DATEADD([DAY], 1, GETDATE());
SELECT DATEADD("DAY", 1, GETDATE());
But this will fail:
SELECT DATEADD('DAY', 1, GETDATE());
Interestingly this will also fail (just further evidence that this is being handled like an identifier):
SET QUOTED_IDENTIFIER OFF;
SELECT DATEADD("DAY", 1, GETDATE());
You can't write your own functions or procedures that take identifiers as input - they are always either interpreted as an implicitly converted string (as in EXEC sp_who active;) or they simply fail a parse check (as in the above). Input parameters to built-in and user-defined functions will take expressions, but procedures will not.
