We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Optional parameters in CREATE FUNCTION.

doc_jer
doc_jer asked
on
Medium Priority
573 Views
Last Modified: 2007-12-19
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?
Comment
Watch Question

Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
Other known limitations are
- you can't use exec() inside a UDF
- you can't use non-deterministic functions (like getdate())

Author

Commented:
so its only stored proc

Commented:
Yes, only in stored procedures

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

Author

Commented:
ok thanks...

Commented:
>>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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.