• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 522
  • Last Modified:

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?
0
doc_jer
Asked:
doc_jer
  • 4
  • 2
1 Solution
 
HilaireCommented:
User - defined functions don't support optional parameters
0
 
HilaireCommented:
Other known limitations are
- you can't use exec() inside a UDF
- you can't use non-deterministic functions (like getdate())
0
 
doc_jerAuthor Commented:
so its only stored proc
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
HilaireCommented:
Yes, only in stored procedures

another limitation of UDFs is that you can't insert/update/delete
0
 
doc_jerAuthor Commented:
ok thanks...
0
 
HilaireCommented:
>>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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now