Link to home
Start Free TrialLog in
Avatar of doc_jer
doc_jer

asked on

Optional parameters in CREATE FUNCTION.

why optional parameters can't work on CREATE FUNCTION?

ex.

CREATE FUNCTION dbo.MyFunc
      (@param1       datetime,
      @param2      int = 0)      -- default value is zero.
 .
 .
 .

dbo.MyFunc(getdate())

it prompt an error "An insufficient number of arguments were supplied for the procedure or function dbo.MyFunc."

is there a way to use optional parameters in CREATE FUNCTION?
ASKER CERTIFIED SOLUTION
Avatar of Hilaire
Hilaire
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Other known limitations are
- you can't use exec() inside a UDF
- you can't use non-deterministic functions (like getdate())
Avatar of doc_jer
doc_jer

ASKER

so its only stored proc
Yes, only in stored procedures

another limitation of UDFs is that you can't insert/update/delete
Avatar of doc_jer

ASKER

ok thanks...
>>another limitation of UDFs is that you can't insert/update/delete<<
Actually you can insert/update/delete , but only in #temp tables or tabel @variables
created inside the scope of the function