Solved

Optional parameters in CREATE FUNCTION.

Posted on 2004-04-02
6
455 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?
0
Comment
Question by:doc_jer
  • 4
  • 2
6 Comments
 
LVL 26

Accepted Solution

by:
Hilaire earned 50 total points
ID: 10739336
User - defined functions don't support optional parameters
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 10739344
Other known limitations are
- you can't use exec() inside a UDF
- you can't use non-deterministic functions (like getdate())
0
 

Author Comment

by:doc_jer
ID: 10739348
so its only stored proc
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 26

Expert Comment

by:Hilaire
ID: 10739361
Yes, only in stored procedures

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

Author Comment

by:doc_jer
ID: 10739362
ok thanks...
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 10739375
>>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

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question