Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server Select Statement Coalesce

Posted on 2014-10-01
5
Medium Priority
?
236 Views
Last Modified: 2014-10-01
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
Comment
Question by:yanci1179
  • 2
  • 2
5 Comments
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40354879
select *
  from #temp
  where col = isnull(@column,0)
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40354881
Does this work?

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

Open in new window

0
 

Author Comment

by:yanci1179
ID: 40354943
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
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40354957
Belt and braces.

Where @column is null or col=@column

Open in new window

0
 

Author Closing Comment

by:yanci1179
ID: 40355177
Awesome!! thank you!!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question