Allow to Pass Optional Parameters to a Stored Procedure

Mount Carrigain NH 1427mIf 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

 

Conclusion: Note that string PARAMETERS are controlling the way SQL select statement is built. Also note that UNION is used to add one *EMPTY* row that will allow GridView to display footer (INSERT) row, even if no records were found.
(Visited 1,879 times, 1 visits today)

Be the first to comment

Your question, correction or clarification Ваш вопрос, поправка или уточнение