Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Passing default values of parameters to Table Valued Functions

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.

like image 573
wootscootinboogie Avatar asked Mar 28 '13 19:03

wootscootinboogie


People also ask

Can you assign the default values to a function parameters?

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.

What is table-valued function?

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.


Video Answer


1 Answers

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.

like image 159
Aaron Bertrand Avatar answered Oct 18 '22 14:10

Aaron Bertrand