Skip to content
Advertisement

SQL Server: Optional variable in a stored procedure

I would like to know if there is anyway I can set one of my stored procedure parameter as optional.

IF @thing_id <> ''
BEGIN 
 SET @sFiltre = @sFiltre + ' AND OPERES.OPE_THING = ' +  CONVERT(VARCHAR,@thing_id)
END

Advertisement

Answer

Providing a default value to the stored procedure parameter will make it optional.

EDIT:

CREATE PROC [ EDURE ] [ owner. ]
procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,…n ]

default

Is a default value for the parameter. If a default is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL. It can include wildcard characters (%, _, [], and [^]) if the procedure uses the parameter with the LIKE keyword.

Please see SQL Server Documentation: Specifying Parameter Default Values

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement