How to use optional parameter in stored procedure

Question

I have a stored procedure which looks like

  CREATE PROCEDURE Update-all  
 (  @RowID INT,
    @Parameter1 NVARCHAR(50),
    @Parameter2 INT
 )
As
....

i will be passing @Parameter1 / @Parameter2 based on different conditions. if i don't pass @Parameter1 / @Parameter2 ,i am getting a error the stored procedure expects @parameter1/@parameter1 which is not supplied . Any ideas how to fix this issue?

Answer 1

Add some default values to parameters:

CREATE PROCEDURE Update-all  
 (  @RowID INT,
    @Parameter1 NVARCHAR(50) = '',
    @Parameter2 INT = 0
 )

Answer 2

You need to supply default values for the parameters.

  CREATE PROCEDURE Update-all  
 (  @RowID INT,
    @Parameter1 NVARCHAR(50) = NULL,
    @Parameter2 INT = NULL
 )
As
....

Answer 3

You need to provide defaults for your parameters. e.g.

create procedure Test1 (
   @rowid int,
   @Param1 nvarchar(50) = null,
   @Param2 int = 0 )
as
   print 'rowid=' + convert(varchar(20), @rowid)
   print 'param1=' + @Param1
   print 'param2=' + convert(varchar(20), @Param2)

exec Test1 5
exec Test1 5, 'testing'
exec Test1 5, 'testing', 198