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.
Default parameter in JavascriptThe default parameter is a way to set default values for function parameters a value is no passed in (ie. it is undefined ). In a function, Ii a parameter is not provided, then its value becomes undefined . In this case, the default value that we specify is applied by the compiler.
A table function, also called a table-valued function (TVF), is a user-defined function that returns a table. You can use a table function anywhere that you can use a table. Table functions behave similarly to views, but a table function can take parameters.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With