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