SQL Replace an empty SQL SELECT with word

Question

I'm trying to solve the following problem:

I would like to make a select, when the result is empty it should be replaced with 'empty' Else the result should be there. That is my try:

select case (count*) 
       when 0 then 'empty'
       ELSE
       THEVALUEOFTHECOLUM
END AS RESULT

from Database.table where CarID = 12;

Thanks for every comment.

stackoverflow.com/questions/7687717/…

Paul
– Paul

2012-08-08 13:07:22 +00:00
Commented Aug 8, 2012 at 13:07 — Paul
– Paul, Commented Aug 8, 2012 at 13:07

Peter Lang · Accepted Answer · 2012-08-08 13:01:47Z

2

This should work, but you might have to convert the second occurrence of COUNT(*) to VARCHAR depending on the database used:

SELECT
  CASE WHEN COUNT(*) = 0
    THEN 'empty'
    ELSE COUNT(*) -- CONVERT, TO_CHAR, ...
  END AS result
FROM Database.table where CarID = 12;

answered Aug 8, 2012 at 13:01

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Chris Over a year ago

That will only return the count, if you want the column you need replace the second count with MAX(collname), but that will only return one entry.

Chris Over a year ago

MAX is needed because Else want's to return one result. You can use any aggregate function

XN16 · Accepted Answer · 2012-08-08 13:09:23Z

0

SELECT 
CASE
    WHEN Q.countvalue = 0 THEN 'Empty' 
    ELSE CONVERT(NVARCHAR(10), Q.countvalue) 
END AS RESULT
FROM 
    (
    SELECT COUNT(*) AS countvalue
    FROM   Database.table WHERE CarID = 12
    ) AS Q

answered Aug 8, 2012 at 13:09

XN16

5,88916 gold badges52 silver badges76 bronze badges

Comments

Chris · Accepted Answer · 2012-08-08 13:11:58Z

0

This feels hacky to me, but it will return the column data. It is not one query, but it's still setwise.

declare @tmp table (id int)
declare @cnt int
insert into @tmp select col from table
select @cnt = count(*) from @tmp
if(@cnt = 0)
begin
    select 'empty'
end
else
begin
    select * from @tmp
end

answered Aug 8, 2012 at 13:11

Chris

4,1235 gold badges36 silver badges51 bronze badges

2 Comments

John Smithv1 Over a year ago

I want all colums, how can I make your code work? Is there a procedure needed?

Chris Over a year ago

@John I don't understand the question. Where do you want all columns?

John Smithv1 · Accepted Answer · 2012-08-16 08:46:53Z

0

Is it possible to code it with one query? If there are no results -> no result found else Show all results, not only one

declare @tmp table (id int) 
declare @cnt int 
insert into @tmp select col from table 
select @cnt = count(*) from @tmp 
if(@cnt = 0) 
begin     
select 'empty' 
end 
else 
begin     
select * from @tmp 
end

answered Aug 16, 2012 at 8:46

John Smithv1

7035 gold badges14 silver badges33 bronze badges

Collectives™ on Stack Overflow

SQL Replace an empty SQL SELECT with word

4 Answers 4

2 Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related