How to convert complex block condition in to MSSQL procedure

Question

I have some checkbox(Condition Filter) which user can tick select filter condition

for example:

CheckBox 1 : OrderNumber
CheckBox 2 : Driver
CheckBox 3 : Code
...

and pass into stored procedure - some thing like this :

ALTER PROCEDURE [dbo].[DailyBatchSummaryReport_NoGroup]
@FromDatePara Datetime,
@ToDatePara Datetime,
@SeleRecipe nvarchar(max),
@SeleOrder nvarchar(max),
@SeleCustomer nvarchar(max),
@SeleSite nvarchar(max),
@SeleTruck nvarchar(max),
@SeleDriver nvarchar(max),
AS
BEGIN

// Stuck at this point

I want to write one procedure can handle muti-case of user selection, I have idea with string builder and pass in to WHERE clause, but SQLSERVER not accept this (It accept boolean parameter)

could someone help me. Thanks you.

Can you give the clear information on what you want and provide sample data that may help us to give the correct answer. — Mano, Feb 27 '19 at 04:45
you can use conditional where clause as described in this link https://stackoverflow.com/questions/10521677/how-do-i-create-a-conditional-where-clause — Mukesh Arora, Feb 27 '19 at 05:18

score 0 · Accepted Answer · answered Feb 27 '19 at 05:48

You can use it as follows

ALTER PROCEDURE [dbo].[DailyBatchSummaryReport_NoGroup]
@FromDatePara Datetime,
@ToDatePara Datetime,
@SeleRecipe nvarchar(max),
@SeleOrder nvarchar(max),
@SeleCustomer nvarchar(max),
@SeleSite nvarchar(max),
@SeleTruck nvarchar(max),
@SeleDriver nvarchar(max),
AS
BEGIN

Pass @SeleOrder , @SeleCustomer values as 0 for unchecked and 1 for checkbox checked

select * from
yourtable
where 
OrderNumber= CASE WHEN @SeleOrder = '0' THEN OrderNumber ELSE @SeleOrder END
AND
CUSTOMER= CASE WHEN @SeleCustomer = '0' THEN CUSTOMER ELSE @SeleCustomer END
AND
Driver= CASE WHEN @SeleDriver = '0' THEN Driver ELSE @SeleDriver END

Make sure you pass default value as 0. Here you have choose datatype as nvarchar(max) so I have written '0' else you can write 0 if you pass just int value. or you can set default value as 0

 ALTER PROCEDURE [dbo].[DailyBatchSummaryReport_NoGroup]
    @FromDatePara Datetime,
    @ToDatePara Datetime,
    @SeleRecipe int  = 0,
    @SeleOrder int  = 0,
    @SeleCustomer int  = 0,
    @SeleSite int  = 0,
    @SeleTruck int  = 0,
    @SeleDriver int  = 0,
    AS
    BEGIN

choose the logic as per your coding convenience. but above method will work

Thanks for your great. This method work expected. Hope this answer can help some one else to solve their problems similar me. — Bui Quy, Feb 27 '19 at 06:33

score 0 · Answer 2 · answered Feb 27 '19 at 05:54

0

May be you can try somthing like this in where condition:-

If user selects check box then pass respective value or pass null.

So your where condition can be like

Where
(Colname1 = @Paramenter1 or @Paramenter1 is null)
and 
(Colname2 = @Paramenter2 or @Paramenter2 is null)

.... so on....

answered Feb 27 '19 at 05:54

Mahesh

198
2
16

I will try your method and feedback ASAP. Thank for your answer – Bui Quy Feb 27 '19 at 06:40

How to convert complex block condition in to MSSQL procedure

2 Answers2