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

Introduction

Nullable parameters in T-SQL procedures or functions without default values can lead to unexpected runtime errors, as they still require a value during invocation, complicating the execution process.

Description

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:

-- Example of nullable parameter without default value
CREATE PROCEDURE SampleProcedure
    @Parameter1 INT = NULL,
    @Parameter2 INT -- nullable without default
AS
BEGIN
    SELECT @Parameter1, @Parameter2;
END;

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.

How to fix

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:

-- Corrected example with default value for nullable parameter
CREATE PROCEDURE SampleProcedure
    @Parameter1 INT = NULL,
    @Parameter2 INT = NULL -- Now includes a default value
AS
BEGIN
    SELECT @Parameter1, @Parameter2;
END;

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

3 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE PROCEDURE SA00256.TestProc
  @param1 int
, @param2 int NULL
, @param3 int NULL = NULL
, @param4 int = NULL
AS
BEGIN
    SET NOCOUNT ON;
  /* PROCEDURE BODY */
END

Analysis Results

	Message	Line	Column
1	SA0260 : Parameter defined as nullable, but no default value provided.	3	2

Analysis Rules