SP Problem or is it me

[shiggyshag]

Ok

I have a table with the following Columns

ItemNumber, Hk, Uk, Sg, Eu, Status

Now Hk Uk Sg Eu can be either 1 or 0

Ok so in my sp I would like to return all Items where the Status is 'Avaiable' and Where Hk or Uk or Sq or Eu Are 0

Hope that makes sense any help would be great

Cheers

 

[JamesLean]

SELECT cols
FROM table
WHERE status = 'available'
  AND (hk = 0
  OR uk = 0
  OR sg = 0
  OR eu = 0)

--James

 

[r937]

just to be different...

WHERE status = 'available'
AND 0 in (hk,uk,sg,eu)



r937.com | rudy.ca

 

[shiggyshag]

Hi

I tried that but still not working


item Status UK sg Eu
38 Available 1 0 0
39 Available 0 1 0
40 Available 1 0 0
41 Available 1 0 0
42 Available 1 0 0
43 Available 0 0 0
44 Available 1 0 0

Ok so looking at the above if I select Available and the O
I only want to return item 43.
If I selct Available and 1 then return the rest.

Cheers

 

[SajidAttar]

SELECT cols
FROM table
WHERE status = 'available'
AND uk = 0
AND sg = 0
AND eu = 0

 

[r937]

WHERE status = 'available'
AND NOT (0 in (hk,uk,sg,eu))




r937.com | rudy.ca

 

[TJRTech]

shiggyshag, your last comment said:

>> Ok so looking at the above if I select Available and
>> the O I only want to return item 43.
>> If I selct Available and 1 then return the rest.

That makes it sound like you have two different conditions or filters (or params) that you are using for selecting the results. In one case you always want available to be set, but in another case, you want to optionally define if any one, or none of the three boolean columns should be set?

Is that the case, or something else?

If so, you might help us by restating exactly how you want the SP to perform, and maybe even list the parameters:

The SP for what I think you described in your last post is as follows:

create procedure spListRecs
  @AllZero bit
as
  SELECT cols
  FROM table
  WHERE status = 'available'
    AND ( 
          ( @AllZero = 1
            AND uk = 0
            AND sg = 0
            AND eu = 0 ) 
         OR
          (
            @AllZero = 0
            AND ( uk = 1
                  OR sg = 1
                  OR eu = 1
                )
          )
        )
return 0