SA0260 : Parameter defined as nullable, but no default value provided

When defining procedure or function parameters in T-SQL code, it’s common to make some parameters nullable. However, if these nullable parameters do not have a default value specified, they still require a value whenever the procedure or function is called. This can lead to unexpected runtime errors and complicate the invocation of these routines.

For example:

1-- Example of nullable parameter without default value
2CREATE PROCEDURE SampleProcedure
3    @Parameter1 INT = NULL,
4    @Parameter2 INT -- nullable without default
5AS
6BEGIN
7    SELECT @Parameter1, @Parameter2;
8END;

In this example, @Parameter2 is defined as nullable but without a default value. When calling SampleProcedure, a value must still be provided for @Parameter2, which may not be the intended behavior and can cause errors if overlooked.

• Causes confusion about parameter requirements, possibly leading to runtime errors.

• Complicates procedure and function invocation as developers need to provide values even for parameters they expect to default.

`

To ensure nullable parameters in stored procedures and functions have a default value, follow these steps:

Follow these steps to address the issue:

1. Identify nullable parameters in your procedures and functions that do not have a default value.

2. Modify the parameter definitions to include a default value using the = operator right after the parameter name and data type.

3. Update your procedures or functions to reflect these changes in their parameter lists to avoid runtime errors and simplify usage.

For example:

1-- Corrected example with default value for nullable parameter
2CREATE PROCEDURE SampleProcedure
3    @Parameter1 INT = NULL,
4    @Parameter2 INT = NULL -- Now includes a default value
5AS
6BEGIN
7    SELECT @Parameter1, @Parameter2;
8END;

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

Design Rules, Bugs

There is no additional info for this rule.

Other Resources