Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server Select Statement Coalesce

Posted on 2014-10-01
5
Medium Priority
?
226 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 40

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

722 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