How to use Optional Parameters in T-SQL User Defined Functions
If as a t-sql developer or a SQL Server database administrator in your company you work a lot with sql user defined functions, you might probably require optional parameters in udf's.
Optional parameters are widely used in programming as well as in stored procedures in T-SQL.
But optional declaration and logic is a little bit different when user defined function is the topic.
The trick that enables a work around for declaring optional parameters for t-sql functions is checking the parameter with ISNULL() within the sql function definition and calling the function with NULL values where you want to use default value for the optional parameter.
When optional parameter value is passed as NULL then ISNULL() will replace the parameter value with default value of the parameter.
Here is the sample t-sql udf function which uses optional parameter.
CREATE FUNCTION OptionalParameters(
@i int,
@optional_Parameter int
) RETURNS int
BEGIN
-- may be omitted if you use directly the default values
DECLARE @Default_Value int
SET @Default_Value = 0
RETURN @i + ISNULL(@optional_Parameter, @Default_Value)
-- OR use default value directly as
--RETURN @i + ISNULL(@optional_Parameter, 0)
END
GO
SELECT
dbo.OptionalParameters(1, 2),
dbo.OptionalParameters(1, null)
And above sample sql select statement shows the usage of optional parameter in user defined t-sql function.
|