Allow to Pass Optional Parameters to a Stored Procedure

If you have several search boxes on a Web page and some boxes can be blank, how do you pass values of those search boxes into SQL stored procedure (SP)?  Pass them as empty strings, and then process the values here in SP.

In this example a series of IF statements build an SQL string @sql, and then EXEC statement executes the resulting SQL command.

USE [YourDB]
GO
/****** Object:  StoredProcedure [dbo].[qTasksSubstituteNULLS]
        Script Date: 12/10/2019 21:30:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Yuor Name
-- Create date: 2019-12-09 WE
-- Description: Substitute String Params with Bool
-- =============================================
ALTER PROCEDURE [dbo].[qTasksSubstituteNULLS]
-- Add the parameters for the stored procedure here
@taskDoneCHAR nvarchar(16) = '*ALL',
@taskCategoryCHAR nvarchar(16) = '*ALL'
AS
DECLARE
@sql nvarchar(1000) = N'SELECT * FROM tTaskMaster ',
@taskDoneYN bit = NULL,
@taskCategory nvarchar(16) = NULL;
-- Begin substitutin CHAR with BOOL parameters
IF @taskDoneCHAR = 'Open' SET @taskDoneYN = 0
IF @taskDoneCHAR = 'Completed' SET @taskDoneYN = 1
IF @taskCategoryCHAR != '*ALL' SET @taskCategory = @taskCategoryCHAR
IF @taskDoneYN IS NOT NULL  AND @taskCategory IS NOT NULL
SET @sql = @sql + N' WHERE taskDoneYN = @taskDoneYN AND taskCategory = @taskCategory '
ELSE
BEGIN
IF @taskDoneYN   IS NOT NULL SET @sql = @sql + N' WHERE taskDoneYN   = @taskDoneYN '
IF @taskCategory IS NOT NULL SET @sql = @sql + N' WHERE taskCategory = @taskCategory '
END ;
SET @sql = @sql + N' UNION SELECT * FROM tTaskMaster WHERE taskID = 10 '
SET @sql = @sql + N' ORDER BY taskPriority, taskDateDue '
EXEC sp_ExecuteSQL @sql, N'@taskDoneYN bit, @taskCategory nvarchar(16)', @taskDoneYN, @taskCategory