?
Solved

Optional parameters in CREATE FUNCTION.

Posted on 2004-04-02
6
Medium Priority
?
491 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 26

Accepted Solution

by:
Hilaire earned 200 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

764 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