18
create function Xtest
(@d1 varchar(3)=null
,@d2 varchar(3)=null)
returns table 
as
return 
with code_list(code_pattern)
as
(
    select x.code_pattern
        from (values (@d1),(@d2)) as x(code_pattern)
        where x.code_pattern is not null
),y
as
(
    select substring(code,1,3) as code
        from tblicd
        where substring(code,1,3) in 
        (
            select * from code_list
        )
)

select * from y

is my function which will make sense when it's fully finished. If I try to run this query and I don't supply two parameters, it will fail. I have the same code as a stored procedure and if I only enter one parameter, it works fine and assigns the second parameter null. Is there a way that I can pass null as a parameter value if the parameter isn't supplied, like one can do with stored procedures?

The function does compile.

wootscootinboogie
  • 8,461
  • 33
  • 112
  • 197
  • 1
    Related, at least one is probably a dupe : http://stackoverflow.com/questions/3278860/optional-parameter-in-sql-server ... http://stackoverflow.com/questions/12721869/alter-a-sql-server-function-to-accept-new-optional-parameter ... http://stackoverflow.com/questions/8358315/t-sql-function-with-default-parameters – Aaron Bertrand Mar 28 '13 at 19:38

1 Answers1

48

You can't omit the parameters when you call a function. This isn't anything you're doing wrong in the syntax, it's just simply not supported by SQL Server. Stored procedures have optional parameters, but functions do not.

You can, however, supply default:

SELECT code FROM dbo.Xtest(default, default);

Or in this case if you know the defaults are NULL you can do:

SELECT code FROM dbo.Xtest(NULL, NULL);

Also please always use the schema prefix (in this case dbo.) when creating and referencing any object, especially functions.

sandwood
  • 2,038
  • 20
  • 38
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490