CODEWITHSUNDEEP
sqlcase
John Smithv1
John Smithv1

Reputation: 703

SQL Replace an empty SQL SELECT with word

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.

Upvotes: 0

Views: 516

Answers (4)

John Smithv1
John Smithv1

Reputation: 703

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

Upvotes: 0

Chris
Chris

Reputation: 4083

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

Upvotes: 0

XN16
XN16

Reputation: 5889

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

Upvotes: 0

Peter Lang
Peter Lang

Reputation: 55594

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;

Upvotes: 2

Related Questions