Am I right in thinking a proc can have optional parameters - which if you miss out when calling the proc, take the value put as the default.

But in a UDF all parameters must be passed something, even if this is onlt DEFAULT, or NULL.

If so why the differnace between the two?

Cheers

I didn't know that about UDFs, but I think if you are using UDF's for select code, then you should be using stored procedures or views

User defined functions are really designed for very lightweight formatting code such as converting an american date to an English date, as they can be used in line with select statements. They should not be used for selecting large recordsets.

The only reason people have ever used them for large selects is because of MS Access not really liking stored procedures.

Pauldohert,

One of the reasons that I use parameters defined as optional in UDFs is to provide for allowing different type of processing in the UDF if the optional parameter is present or not. I no not normally try to just provide a default if the optional parameter is not there.

For example, somewhere in my function I may simple have a statement like:
If isnull(MyOptionalValue) then
.....
end if

HTH