T-Sql condition check

Question

I have a table where there is column called allocated_to. If there is a value in that column, that means the status of the row is assigned otherwise unassigned.

From my search box i am sending 1 for assigned and 0 for unassigned. Also I have two more similar checks for pending and closed on column named SignOff (type: int).

Now we have total of 9 search criteria

1. Pending
2.Closed
3. Unassigned
4. Assigned
5. Pending + Unassigned
6. Pending + Assigned
7. Closed + Unassigned
8. Closed + Assigned
9. For all records irrespective of any statuses.

So How do I add condition to my query. Actually its a change in SP and SP for that is already up and running. So I cant make a huge change in my query, by making it dynamic or whatsoever.

I can give you a sample here , how my query looks like:

If Some_Condition
 begin
   Select x,y,zfrom T1 join T2 on t1.a=t2.b
   Where IsNull(SignOff,0)=@ParamForPendingAndClosed
 end

Now I would like to add my above 9 check in this where, Any help??

Please note:

I can't take heavy alterations, as I need it to make in each and every if-else condition. Query has almost 4-5 if else depending upon its header condition, please do not suggest me to go for dynamic procedures. Other than that are welcome.

For 9, `Some_Condition` can be `1=1` or something that always evaluates to true — mr.Reband, Aug 26 '13 at 15:17

score 0 · Answer 1 · edited May 23 '17 at 10:25

0

You could add a couple of optional parameters that allow you to specify whether results should include Pending/Closed or Assigned/Unassigned results. This way you maintain a single, flexible way of retrieving results.

Take a look at this SO Answer for an overview of using Optional Parameters in SQL.

edited May 23 '17 at 10:25

Community

1
1

answered Aug 26 '13 at 15:35

STW

44,917
17
105
161

score 0 · Answer 2 · answered Aug 26 '13 at 15:43

So, If i understand correctly, you can filter on two criteria independently. Option 1 is "Pending", "Closed" or "dont-filter", and option three is "Assigned", "Unassigned" and "dont-filter". "dont-filter" means that that criterion is not used in the filtering of items, i.e. the items returned can have any value for that parameter.

Combining these two filters you get your 3 X 3 = 9 possible scenarios.

What I would do is use the values of the parameters sent to the procedure to mold the query to my needs, along these lines:

create procedure getItems
   @Status int, -- 0 - don't filter, 1 - pending, 2 - closed
   @Assignment int -- 0 - don't filter, 1 - assigned, 2 - not assigned
as
begin
  select * from Items
  where ((@Status = 0) 
          and ((@Assignment = 0) -- no filter at all
              or (@Assignment = 1  and allocated_to is not null) 
              or (@Assignment = 2  and allocated_to is null)))
    or  ((@Status = 1 and pending is not null) 
          and ((@Assignment = 0) 
              or (@Assignment = 1  and allocated_to is not null) 
              or (@Assignment = 2  and allocated_to is null)))
   or  ((@Status = 2 and closed is not null) 
          and ((@Assignment = 0) 
              or (@Assignment = 1  and allocated_to is not null) 
              or (@Assignment = 2  and allocated_to is null)))
end

SQL Server is smart enough to use parameter sniffing to optimize the actual query run, so basically, if you send 0, 0 as your parameters, the query will execute as

select * from Items

and if you send 1, 2 the query will execute as

select * from Items
where pending is not null
  and allocated_to is null

Your answer seems quite close enough to what I was looking for — , Aug 26 '13 at 18:48

T-Sql condition check

2 Answers2