yanci1179
asked on
SQL Server Select Statement Coalesce
create table #temp
(col int)
insert into #temp
select 1
union select null
declare @column as int
set @column = null
select *
from #temp
where col = coalesce(@column,col)
the sample table above has two records. one null and one with a value. If the parameter is null, I would like both records to be returned, however if the value = 1, then I would like to only return the value that equals 1. I think I've done this with coalesce once before, however I can't recall the exact script.
Thoughts?
(col int)
insert into #temp
select 1
union select null
declare @column as int
set @column = null
select *
from #temp
where col = coalesce(@column,col)
the sample table above has two records. one null and one with a value. If the parameter is null, I would like both records to be returned, however if the value = 1, then I would like to only return the value that equals 1. I think I've done this with coalesce once before, however I can't recall the exact script.
Thoughts?
Does this work?
where not(col <> coalesce(@column,col))ASKER
Hi Kyle, the statement doesn't return anything when @column is null.
Hi Phillip,
the statement only returns 1 record when the @column is null.
Thank you both. What I would like is if @column is null all records to show up. if @column = 1, then only return the matching records.
Hi Phillip,
the statement only returns 1 record when the @column is null.
Thank you both. What I would like is if @column is null all records to show up. if @column = 1, then only return the matching records.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome!! thank you!!
from #temp
where col = isnull(@column,0)