SQL Server procedure where IS NULL or parameter like give wrong result

Question

I have a table like this

CREATE TABLE [dbo].[TEST_FIND]
(
    [UNIT1] [nvarchar](50) NULL,
    [UNIT2] [nvarchar](50) NULL,
    [UNIT3] [nvarchar](50) NULL,
    [UNIT4] [nvarchar](50) NULL
) ON [PRIMARY]

and data in that table looks like this

UNIT1 | UNIT2 | UNIT3 | UNIT4
a     | b     | c     | d
null  | b     | null  | null
null  | null  | c     | null
null  | null  | null  | d
a     | null  | null  | null

and my stored procedure is:

CREATE PROCEDURE [dbo].[A_TEST]
    @UNIT1 nvarchar(50) = NULL,
    @UNIT2 nvarchar(50) = NULL,
    @UNIT3 nvarchar(50) = NULL,
    @UNIT4 nvarchar(50) = NULL
AS
BEGIN
    SELECT
        UNIT1, UNIT2, UNIT3, UNIT4
    FROM
       TEST_FIND
    WHERE
       @UNIT1 is null or UNIT1 like '%'+@UNIT1+ '%' AND
       @UNIT2 is null or UNIT2 like '%'+@UNIT2+ '%' AND
       @UNIT3 is null or UNIT3 like '%'+@UNIT3+ '%' AND
       @UNIT4 is null or UNIT4 like '%'+@UNIT4+ '%'
END

The problem is, when I call my procedure like this:

exec A_TEST 'a', null, null, null

the result is ok (just show from table TEST_FIND where UNIT 1 is 'a')

but when use

exec A_TEST null, 'b', null, null

the result is

select * from TEST_FIND

I don't know why, and what's the problem

Need fix this soon, help me

Thanks

Dai · Accepted Answer · 2015-10-08 03:40:17Z

1

What happens when you group your WHERE clauses? Like so:

WHERE
   ( @UNIT1 is null or UNIT1 like '%'+@UNIT1+ '%' )
   AND
   ( @UNIT2 is null or UNIT2 like '%'+@UNIT2+ '%' )
   AND
   ( @UNIT3 is null or UNIT3 like '%'+@UNIT3+ '%' )
   AND
   ( @UNIT4 is null or UNIT4 like '%'+@UNIT4+ '%' )
END

answered Oct 8, 2015 at 3:40

Dai

158k31 gold badges314 silver badges438 bronze badges

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

Comments

Jayanti Lal · Accepted Answer · 2015-10-08 03:45:14Z

0

use brackets in AND condition.

 CREATE PROCEDURE [dbo].[A_TEST]
@UNIT1 nvarchar(50) = NULL,
@UNIT2 nvarchar(50) = NULL,
@UNIT3 nvarchar(50) = NULL,
@UNIT4 nvarchar(50) = NULL
   AS
     BEGIN
       SELECT
        UNIT1,
     UNIT2,
     UNIT3,
     UNIT4
FROM
   TEST_FIND
    WHERE
  (@UNIT1 is null or UNIT1 like '%'+@UNIT1+ '%') AND
  (@UNIT2 is null or UNIT2 like '%'+@UNIT2+ '%' )AND
  (@UNIT3 is null or UNIT3 like '%'+@UNIT3+ '%')AND
  (@UNIT4 is null or UNIT4 like '%'+@UNIT4+ '%')
END

answered Oct 8, 2015 at 3:45

Jayanti Lal

1,1836 silver badges19 bronze badges

Comments

Miyuru Ratnayake · Accepted Answer · 2015-10-08 03:47:35Z

0

Apply parathesis for the OR coditions as follows:

CREATE PROCEDURE [dbo].[A_TEST]
@UNIT1 nvarchar(50) = NULL,
@UNIT2 nvarchar(50) = NULL,
@UNIT3 nvarchar(50) = NULL,
@UNIT4 nvarchar(50) = NULL
AS
BEGIN
SELECT
   UNIT1,
   UNIT2,
   UNIT3,
   UNIT4
FROM
  TEST_FIND
WHERE
   (@UNIT1 is null or UNIT1 like '%'+@UNIT1+ '%') AND
   (@UNIT2 is null or UNIT2 like '%'+@UNIT2+ '%') AND
   (@UNIT3 is null or UNIT3 like '%'+@UNIT3+ '%') AND
   (@UNIT4 is null or UNIT4 like '%'+@UNIT4+ '%')
  END

answered Oct 8, 2015 at 3:47

Miyuru Ratnayake

4563 silver badges11 bronze badges

Collectives™ on Stack Overflow

SQL Server procedure where IS NULL or parameter like give wrong result

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest