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 
Add a comment |
5 Answers
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
Comments
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
Comments
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 
2 Comments
shahkalpesh
@Joel: Is it necessary to have the optional parameters, towards the end? I think, it is better to do it from usability point of view. Is that right?
Joel Coehoorn
No, but it's definitely a best practice.
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.
Comments
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
1 Comment
nicko
"Dynamic SQL = SQL Injection" is not necessarily true. It depends on if you use user supplied values to build up your dynamic SQL string or not. You could easily build a dynamic SQL string based solely on logic within the stored procedure, then pass user supplied values into the dynamic statement via parameters. The OP's code would have been open to SQL injection, but not all Dynamic SQL is.