Adding parameters to where clause sql

Question

I am something of a beginner to SQL and I am trying to run the following SP.

DECLARE @stringStatus varchar(100)

--Check for status value
IF @Status is NULL
BEGIN
    set @stringStatus = ''
END
ELSE 
BEGIN
    set @stringStatus = ' and ps.Status = ' + CAST(@Status as varchar)
END

select * from Projects p
    join projectstatus ps on p.pid = ps.pid
    where ps.Category = isnull(@Category, p.Category) + @stringStatus

The aim of the above is to get all rows if @Status is NULL, and to filter the rows, if a parameter has been assigned to @Status.
@Category (varchar) and @Status (int) are IN paramateres

This works fine when @Status is NULL, i.e, I get all the records. But if I pass a parameter, say, @Status = 2, the execution returns no rows, even though there are a few records available.

First of all, how do I get my desired results? Secondly, is there a better way to do this without an if condition block?

vitalygolub · Accepted Answer · 2017-02-27 11:15:15Z

1

Actually, your result is

select * from something where ps.Category ='some string, containing and ps.Status= inside'

So empty rowset is expected result.
You want something like this (hope status is number, not string)

select * from Projects p
    join projectstatus ps on p.pid = ps.pid
    where ps.Category = isnull(@Category, p.Category) and
     (@Status is NULL OR ps.Status = @Status)

Ok, here are tests for mistrustful :-)

declare @projects table 
(
    pid int,
    name nvarchar(20),
    category int
);

declare @projectstatus table
(
    pid int,
    Category int,
    status int
);

insert into @projects values
(1,'Project 1', 1),(2,'Project 2',1),(3,'Project 3',1),(4,'Project 4',1),(5,'Project 5',1);

insert into @projectstatus values
(1,1,1),(2,1,2),(3,1,3),(4,1,2),(5,1,NULL);


declare @Category int =null;
declare @Status int;

--first of all, do not understand, what is the logic with category
--category in one table should be the same, than in other table or specified?
--ok, you said with category everything is ok, do not test category, test status 

--test with null
set @Status=null


select * from @Projects p
    join @projectstatus ps on p.pid = ps.pid
    where ps.Category = isnull(@Category, p.Category) and
     (@Status is NULL OR ps.Status = @Status)

--test with existing status     
set @Status=1
select * from @Projects p
    join @projectstatus ps on p.pid = ps.pid
    where ps.Category = isnull(@Category, p.Category) and
     (@Status is NULL OR ps.Status = @Status)

--test with not existing status     
set @Status=10
select * from @Projects p
    join @projectstatus ps on p.pid = ps.pid
    where ps.Category = isnull(@Category, p.Category) and
     (@Status is NULL OR ps.Status = @Status)

edited Feb 27, 2017 at 11:15

answered Feb 27, 2017 at 10:50

vitalygolub

7353 silver badges18 bronze badges

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

2 Comments

Sanketh. K. Jain Over a year ago

(@Status is NULL OR ps.Status = @Status) is always going to return the full list. It will never filter.

vitalygolub Over a year ago

Do not agree. For example @Status =1, will return rows with status 1 and do not show row with status 2. I can even give example with temp tables

Devendra-Systematix · Accepted Answer · 2017-02-27 11:16:53Z

0

You can simple put condition in the below way for your desired results

--Check for status value
IF @Status is NULL
  BEGIN
    select * from Projects p
    join projectstatus ps on p.pid = ps.pid
    where ps.Category = isnull(@Category, p.Category) 
  END
ELSE 
  BEGIN
    select * from Projects p
    join projectstatus ps on p.pid = ps.pid
    where ps.Category = isnull(@Category, p.Category) + @stringStatus
  END

Thanks

answered Feb 27, 2017 at 11:16

Devendra-Systematix

967 bronze badges

Collectives™ on Stack Overflow

Adding parameters to where clause sql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related