Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

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?
0
yanci1179
Asked:
yanci1179
  • 2
  • 2
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
select *
  from #temp
  where col = isnull(@column,0)
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Does this work?

where not(col <> coalesce(@column,col))

Open in new window

0
 
yanci1179Author Commented:
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.
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Belt and braces.

Where @column is null or col=@column

Open in new window

0
 
yanci1179Author Commented:
Awesome!! thank you!!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now