SQL - Create Stored Procedures with optional parameters

If you want to make your code generic, say, sometimes you want to put certain criteria to the code to make it run faster,  and sometimes you just want to see all data no matter how long it will take. You can specify your declared parameter part like this:


ALTER PROCEDURE  dbo.sp_NAWAT_MISC_SpotfireInternalQC
(@state_name nvarchar(255) = NULL,
 @play_name  nvarchar(255) =  NULL
 )

 AS
BEGIN 

    ---- your code
           WHERE         (state_name = @state_name OR state_name is null)
                        AND  (play_name = @play_name OR  play_name is null) ;

END 
GO

or
(reference:SQL - Passing multiple values through one parameter in a stored procedure)

 AS
BEGIN 
  SET @Sql = '

    ---- your code