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

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 - http://msdn.microsoft.com/en-us/library/aa258259%28SQL.80%29.aspx

Answer 2

Yes. List "optional" parameters at the end of the parameter list and give them a default value (typically NULL):

CREATE PROCEDURE MyProcedure
    @param1 int,
    @param2 varchar(200),
    @thing_id int = NULL
AS

    If @thing_id IS NULL Begin
       /* ... */
    End

END

Answer 3

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 4

Setting aside the SQL injection joy that code will bring, yes you can. You can set a default value for parameters

CREATE PROCEDURE DoStuff @param1 varchar(20) = null

Then inside the stored procedure

IF @param1 IS NOT NULL
BEGIN
    ... Do stuff
END

You can set the default value to be anything you like.

Answer 5

CREATE PROCEDURE SQL_INJECTION(
@MandatoryA int,
@MandatoryB varchar(50),
@MandatoryC datetime,
@OptionalA varchar(50) = NULL
)
AS

-- PUT YOUR DYNAMIC SQL HERE

GO

To call

EXEC dbo.SQL_INJECTION @MandatoryA = 1, @MandatoryB = 'test', @MandatoryC = '2009-10-05', @OptionalA = DEFAULT

Note1: Dynamic SQL = SQL Injection