How do I get a value from a NULL column?

Question

How can I modify this stored procedure to also return rows with NULL column values?

    @title VARCHAR(50) NULL,
    @initials VARCHAR(100) NULL,
    @surname VARCHAR(100) NULL, 
AS
BEGIN
    SELECT 
        ID, MEMBERSHIP_ID, TITLE, INITIALS, SURNAME, 
    FROM
        MEMBERSHIP_DTLS 
    WHERE 
        TITLE = (CASE WHEN @title IS NULL THEN (TITLE) ELSE @title END)
        AND INITIALS = (CASE WHEN @initials IS NULL THEN INITIALS ELSE @initials END )
        AND SURNAME = (CASE WHEN @surname IS NULL THEN SURNAME ELSE @surname END)
END
GO

What I am trying to accomplish here, is that if a NULL parameter is passed to the procedure then the results returned won't be filtered by that parameter, so if all parameters were null then everything in that table is returned.

The above works only with column values that are not null when a null is passed to all parameters then nothing is returned, the actual stored procedure has many more parameters so I cannot go for a solution where I rewrite the expression to match each combination.

user_1144035 · Answer

I believe the logic you want is:

WHERE (TITLE = @title OR @title IS NULL) AND
      (INITIALS = @initials OR @initials IS NULL) AND
      (SURNAME = @surname OR @surname IS NULL)

user_1118978 · Answer

I changed your query for parameter null value handling. checki it please

SELECT M.ID, M.MEMBERSHIP_ID, M.TITLE , M.INITIALS, M.SURNAME, 
FROM MEMBERSHIP_DTLS M 
WHERE (@title IS NULL OR TITLE = @title)
  AND (@initials IS NULL or INITIALS = @initials )
  AND (@surname IS NULL OR SURNAME = @surname  );

if @title is NULL I don't want it to return NULL values

Try

((@title IS NULL AND TITLE IS NOT NULL) OR TITLE = @title)

user_611545 · Answer

You could use ISNULL as shorthand. This will return NULLS and values matching @title when is has a value, else all records are returned.

AND (@title IS NULL OR TITLE=@title)

user_10532500 · Answer

You can try also the following way to check and match column values with passing variable values.

Description has been given in comment below.

create table #StudentInfo (StudentId int identity(1,1), StudentName char(1))
insert into #StudentInfo values ('A'),( 'B'), ('C'), ('D'), ('E'), (NULL)

declare @StudentNameToSearch varchar(1)
select @StudentNameToSearch = NULL

--To return all student if @StudentNameToSearch is NULL
select * from #StudentInfo where StudentName = @StudentNameToSearch OR @StudentNameToSearch IS NULL

--To return only student of NULL value if @StudentNameToSearch is matched and IS NULL
select * from #StudentInfo where ISNULL(StudentName,'') = ISNULL(@StudentNameToSearch,'')

--To return only student if @StudentNameToSearch is matched and not null
select @StudentNameToSearch = 'B'
select * from #StudentInfo where ISNULL(StudentName,'') = ISNULL(@StudentNameToSearch,'')

The output is as shown below

How do I get a value from a NULL column?

4 Answers