Hi Fabio,

Nulls are a nightmare. If all functions (native and custom) could also return a null we would have a massive increase in the amount of code required after every single function call in order to verify that a correct result was returned. In fact we would probably have to introduce a rule that there could only ever be one function call per line of code - either that or a real mess of NVL()'s or IIF()'s.


a) Then the contrary is true, you must control tons of parameters before calling the function.
If and only if you design the application for use the NULL value like a not known
or not computable value ( not a simple task ! ) and FUNCTIONS return NULL for out-of-range parameters, you remove tons of possible errors to catched.
b) Then MS SQL Server is bad designed ?


Prevention is better than cure. I cannot see why out-of-range-arguments to a procedure should be treated any differently to out-of-range-arguments to a function.


Then why you call the same thing with two different names ( FUNCTION and PROCEDURE ) ?

A simple example:
Which it is the difference between:

? REPLICATE(NULL,10)   && VFP and MSSQL return NULL
and
? REPLICATE('A',NULL)  && VFP and MSSQL return NULL
and
? REPLICATE(NULL,-10)   && VFP and MSSQL return NULL
and
? REPLICATE('A',-10)   && VFP fire a error but MSSQL return NULL

None, you cannot known the result in all the cases,
and your application have not a bug.

If you want known that the count parameters is negative, check it,
it is a logic error use the REPLICATE() error for check the parameters out of range;
if your interest is for the result value,
then the NULL result have all the information that is needed to you.

But MSSQL is not perfect:

? REPLICATE(1,null) && VFP and MSSQL returns NULL, but i want a error for this because it is a probable bug