Menu
It is better to use the " = NULL" if you are adding a new optional parameter to an existing stored proc. The reason is, you may not be aware of ALL the code that calls this proc. Hence, unless you make it optional using the " = NULL", for all the places that you may have missed to pass in a value, it will break.
How to create stored procedure that accepts optional parameter in SQL Server? To create optional parameter in stored procedure, we set the parameter value to NULL while creating a stored procedure.
We can create function with default parameters. To call that function without that parameter, you need to pass NULL to that parameter. Let's say I have function with Two Parameters, in which second parameter can be optional. Let me know if it helps you in any way.
As a program, a stored procedure can take parameters. There are three types of parameters: IN, OUT and INOUT.
You can declare like this
CREATE PROCEDURE MyProcName
@Parameter1 INT = 1,
@Parameter2 VARCHAR (100) = 'StringValue',
@Parameter3 VARCHAR (100) = NULL
AS
/* check for the NULL / default value (indicating nothing was passed */
if (@Parameter3 IS NULL)
BEGIN
/* whatever code you desire for a missing parameter*/
INSERT INTO ........
END
/* and use it in the query as so*/
SELECT *
FROM Table
WHERE Column = @Parameter
Yes, it is. Declare parameter as so:
@Sort varchar(50) = NULL
Now you don't even have to pass the parameter in. It will default to NULL (or whatever you choose to default to).
2014 and above at least you can set a default and it will take that and NOT error when you do not pass that parameter. Partial Example: the 3rd parameter is added as optional. exec of the actual procedure with only the first two parameters worked fine
exec getlist 47,1,0
create procedure getlist
@convId int,
@SortOrder int,
@contestantsOnly bit = 0
as
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With