Stored procedure ignoring Nulls and fetch result for only entered parameters

Question

I am creating a stored procedure to do a search through a table. I have many different search columns, all of which are optional. Is there a way to create a stored procedure that will handle this? Let's say I have a table with four columns ID, N1, N2 and N3. I could do something like this:

Table:

INSERT INTO [dbo].[Sample]
VALUES ('1', 'A,B,C', 'A,B,C', 'A,B,C'),
       ('2', 'B,D,N', 'B,D,N', 'B,D,N'),
       ('3', 'A,N,S', 'A,N,S', 'A,N,S'),
       ('4', 'S,F,G', 'S,F,G', 'S,F,G'),
       ('5', 'D,F,K', 'D,F,K', 'D,F,K'),
       ('6', 'S,H,Y', 'S,H,Y', 'S,H,Y'),
       ('7', 'Z,B,C', 'Z,B,C', 'Z,B,C')

Stored procedure:

CREATE PROCEDURE dbo.Sample2
    @n11 varchar(max) = null,  
    @n12 varchar(max) = null,
    @n21 varchar(max) = null,
    @n22 varchar(max) = null,
    @n31 varchar(max) = null,
    @n32 varchar(max) = null
AS        
BEGIN      
    SELECT COUNT(*) 
    FROM Sample
    WHERE 
        (@n11 IS NULL OR Sample.N1 LIKE '%' + @n11 + '%' 
         OR @n12 IS NULL OR Sample.N1 LIKE '%' + @n12 + '%')
        AND (@n21 IS NULL OR Sample.N2 LIKE '%' + @n21 + '%' 
             OR @n22 IS NULL OR Sample.N2 LIKE '%' + @n22 + '%')
        AND (@n31 IS NULL OR Sample.N3 LIKE '%' + @n31 + '%' 
             OR @n32 IS NULL OR Sample.N3 LIKE '%' + @n32 + '%')
END

If user enters @n11 as A and leave the rest, since N1 contains A in 2 rows, output should be 2 but above query is providing 7. If a parameter is not specified, I need that to be ignored and pass the rest to where condition.

Dale K · Accepted Answer · 2020-03-18 06:35:07Z

1

I think you need to AND every separate input condition e.g.

SELECT count(*)
FROM [Sample] S
WHERE (@n11 IS NULL OR S.N1 LIKE '%'+@n11+'%')
AND (@n12 IS NULL OR S.N1 LIKE '%'+@n12+'%')
AND (@n21 IS NULL OR S.N2 LIKE '%'+@n21+'%')
AND (@n22 IS NULL OR S.N2 LIKE '%'+@n22+'%')
AND (@n31 IS NULL OR S.N3 LIKE '%'+@n31+'%')
AND (@n32 IS NULL OR S.N3 LIKE '%'+@n32+'%')

Note the alias, which is the best practice way to avoid having to repeat a long table name all over the query.

edited Mar 18, 2020 at 6:35

answered Mar 18, 2020 at 3:55

Dale K

28.1k15 gold badges59 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dale K · Accepted Answer · 2020-03-18 10:04:19Z

0

Try to remove the next conditions from your stored procedure query:

@n12 IS NULL
@n22 IS NULL
@n32 IS NULL

As the above conditions are returning always true while there were sent when calling the procedure.

Stored procedure code after modified:

Alter Procedure dbo.Sample2
  @n11 varchar(max) = null,  
  @n12 varchar(max) = null,
  @n21 varchar(max) = null,
  @n22 varchar(max) = null,
  @n31 varchar(max) = null,
  @n32 varchar(max) = null
AS        
BEGIN      
  select count(*)
  from Sample
  where (@n11 IS NULL OR Sample.N1 LIKE '%'+@n11+'%' OR Sample.N1 LIKE '%'+@n12+'%')
  AND (@n21 IS NULL OR Sample.N2 LIKE '%'+@n21+'%' OR Sample.N2 LIKE '%'+@n22+'%')
  AND (@n31 IS NULL OR Sample.N3 LIKE '%'+@n31+'%' OR Sample.N3 LIKE '%'+@n32+'%')
END

Now while executing exec dbo.Sample2 @n11 = 'A' the output will be 2 instead of 7

edited Mar 18, 2020 at 10:04

Dale K

28.1k15 gold badges59 silver badges86 bronze badges

answered Mar 18, 2020 at 3:56

ahmed abdelqader

3,58220 silver badges38 bronze badges

2 Comments

Vijay Krishna Reddy Over a year ago

Working Perfectly..Thanks a lot

Dale K Over a year ago

@VijayKrishnaReddy you realise that with this solution, if you pass a value of @n12 in but leave @n11 as null you will get unfiltered results?

Dale K · Accepted Answer · 2020-03-18 10:11:21Z

0

select
  sum(iif(N1 like '%'+@N11+'%',1,0)  
    + iif(N1 like '%'+@N12+'%',1,0) 
    + iif(N2 like '%'+@N21+'%',1,0) 
    + iif(N2 like '%'+@N22+'%',1,0) 
    + iif(N3 like '%'+@N31+'%',1,0) 
    + iif(N3 like '%'+@N32+'%',1,0) 
  )
from Sample

Any NULL variables will be ignored.

edited Mar 18, 2020 at 10:11

Dale K

28.1k15 gold badges59 silver badges86 bronze badges

answered Mar 18, 2020 at 4:10

Jon Hanlon

1721 silver badge8 bronze badges

Collectives™ on Stack Overflow

Stored procedure ignoring Nulls and fetch result for only entered parameters

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related