stored proc. .. on null .. return everything ?!?!

[unuktine]

hi,

i have this stored proc. , very simple in fact :

-------
CREATE PROCEDURE procstor1
@item1 varchar(10),
@item2 varchar(10),
@item3 varchar(10)
...
as

select * from table1 where
item1 = @item1 and
item2 = @item2 and
item3 = @item3 ...

-------
it's working very well when all variables have some data in each one
but ..
what can I modify on this SP to transform "@item1 = null" in "item1 is not null" ...I mean when @item is null to return "item1<>null" and so on...

10x

D.

 

[amrita418]

DId you mean you want to get all values when input value for a particular column is NULL?

CREATE PROC procstor1
  @item1 varchar(10) = NULL,
  @item2 varchar(10) = NULL,
  @item3 varchar(10) = NULL
AS

SELECT *
FROM table
WHERE (@item1 IS NULL OR item1 = @item1)
  AND (@item2 IS NULL OR item2 = @item2)
  AND (@item3 IS NULL OR item3 = @item3)
...

Regards,
AA

 

[unuktine]

hi !

nope ...

when @item1 = null ... item1 is not null !
when @item1 = 'something' ... item1 = @item1


D.

 

[amrita418]

Try this:

select    *
from      Table
where     (@item1 is null and item1 is not null) 
          or (@item1 is not null and item1 = @item1)

You have to extend to code for other items. Let us know if this is what you were looking for.

Regards,
AA

 

[unuktine]

hi !!!

WOOW ...

yes ...that's it !!!!!!!!! GREAT !! 10XXXX

I thank you so very much for this reply !!!!!!

I save a lot of work with this one !!!
))))

how can I give you a 'helpful post' on this ?!?!?

D.

 

[amrita418]

Glad I could help

Don't bother about the valuable post thing.

Regards,
AA