Stored Procedures  «Prev 

Executing stored procedures and Ordering Parameters

You do not have to specify your parameters in the order that they are declared.
However, if you choose not to do so, you must explicitly indicate which parameters you are assigning values to. You indicate these parameters by using the following format:

@parameter = value [,..n]

An example is:
In this example, the parameter values are sent to SQL Server in the reverse order in which they are declared.

EXECUTE usp_GetLastName @EmployeeStatus = @TempStatus 
OUTPUT, @EmployeeID = 101

Creating a Scalar Function

User-defi ned functions are created, altered, or dropped with the same DDL commands used for other objects; although the syntax is slightly different to allow for the return value:
CREATE FUNCTION FunctionName (InputParameters)
RETURNS DataType
AS
BEGIN;
Code;
RETURN Expression;
END;

The input parameters include a data-type defi nition and may optionally include a default value similar to stored procedure parameters (parameter = default). Function parameters differ from stored procedure parameters in that even if the default is wanted, the parameter is still required to call the function. Parameters with defaults don�t become optional parameters. To request the default when calling the function, pass the keyword DEFAULT to the function.
The following user-defi ned scalar function performs a simple mathematical function. The second parameter includes a default value:
CREATE FUNCTION dbo.ufnCalculateQuotient
(@Numerator numeric(5,2),
@Denominator numeric(5,2)= 1.0)
RETURNS numeric(5,2)
AS
BEGIN;
RETURN @Numerator/@Denominator;
END;
GO
SELECT dbo.ufnCalculateQuotient(12.1,7.45),
dbo.ufnCalculateQuotient (7.0,DEFAULT);

Result:
----------- -----------
1.62       7.00