need an alternative for this simple query

I need to do something like this. how would i do this?
select * from t1
where c1=@.c1
and c2=isnull(@.c2,null)
but i know this wont work because if @.c2 is null it'll try to compare
'c2=null' which should never be true. so how would you do this? thank you.How about:
select * from t1
where c1=@.c1
and COALESCE(c2, '')=iCOALESCE(@.c2,'')
Stu|||wut needs to happen is this...
--when @.c2 is not null
select * from t1
where c1=@.c1
and c2=@.c2
--when @.c2 is null
select * from t1
where c1=@.c1
and c2 is null
How would i do this? I dont think coalesce does it...
sometimes i want to get a value where it's null.|||So in the first case: @.c2 is not null
THe optimizer runs the following query
select * from t1
where c1=@.c1
AND c2 = @.c2 -- if c2 is null, then compare an empty string to @.c2,
which won't match
In the second case, if @.c2 is NULL, then turn it inot an empty string.
You only want columns that are null in the database, and those are also
returned as an empty string. Emptry string=empty string,|||I dont have a problem writing 2 different queries. I need to know if I can
comine the 2 options in one query... thanks. I already have it working with
2
queries...|||try this..
select * from t1
where c1=@.c1
and isnull(c2,0) = isnull(@.c2,0)|||I'm sorry but this wont work also. I need to see if @.c2 is null or not.
if it is not null. I need to 'select * from t1 where c1=@.c1 and c2 = @.c2'
if it's null, I need to 'select * from t1 where c1=@.c1 and c2 is null'
there are null entries in the db and i can't change it. that's how it was
setup... i need to compare @.c2 to a varchar sometime and to NULL sometime...|||I'm sorry, I was trying to explain why the first query I submitted will
work, and obviuosly there is a misunderstanding. Test the query I
submitted the first time:
select * from t1
where c1=@.c1
and COALESCE(c2, '')=COALESCE(@.c2,'')
It will work; I do this often.
Omnibuzz's suggestion is essentially the same as mine, but the
performance will work best depending on the datatype of the column
involved; if a varchar, compare an empty string. If an integer,
compare 0 (assuming that 0 is not a valid value in your colum).|||try this then
select * from t1 where c1=@.c1 and
((c2 is null and @.c2 is null) or (c2 = @.c2))
"tpp" wrote:
> I'm sorry but this wont work also. I need to see if @.c2 is null or not.
> if it is not null. I need to 'select * from t1 where c1=@.c1 and c2 = @.c2'
> if it's null, I need to 'select * from t1 where c1=@.c1 and c2 is null'
> there are null entries in the db and i can't change it. that's how it was
> setup... i need to compare @.c2 to a varchar sometime and to NULL sometime...[/colo
r]|||Hi
create table #t (c1 int ,c2 int,c3 char(1))
insert into #t values (1,10,'a')
insert into #t values (1,null,'b')
select * from #t
declare @.c1 int,@.c2 int
set @.c1=1
set @.c2 =10
if @.c2 is null
select * from #t where c1=@.c1 and c2 is null
else
select * from #t where c1=@.c1 and c2=@.c2
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:F2516C09-2430-48F6-8FBD-2716B77AA640@.microsoft.com...
>I need to do something like this. how would i do this?
> select * from t1
> where c1=@.c1
> and c2=isnull(@.c2,null)
> but i know this wont work because if @.c2 is null it'll try to compare
> 'c2=null' which should never be true. so how would you do this? thank you.