SQL Server: Optional variable in a stored procedure

Question

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

Answer 1

When you create the stored procedure, create it like this

Create Proc MyProc
@Param1 VarChar (20),
@Param2 VarChar (20) = NULL
AS

-- Your code here

GO

Param1 is mandatory

Param2 is Optional

Answer 2

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