CODEWITHSUNDEEP
sqlsql-serversql-server-2005
user380432
user380432

Reputation: 4779

SQL if no rows are returned do this

I have a select statement and I want to say if this select statement does not return any rows then put a '' in every cell. How do I do this?

Upvotes: 5

Views: 24725

Answers (7)

some_yahoo
some_yahoo

Reputation: 39

Here's an example I use for a single column - it's easy and only produces a blank row if there are no matches in the dataset.

select Company from customer where customer=@Company
union
select '' where not exists (select 1 from customer where customer=@Company)

This creates a row of blank(s) when there are no matches.

Upvotes: 0

lols
lols

Reputation: 1

select top 1 isnull(max(col2),' ') as noNullCol from table1 where col1='x'

max returns a null where not have rows then isnull function returns ' ' instead a null value

Upvotes: 0

bobs
bobs

Reputation: 22214

It sounds like you're still not getting all the rows you want. True? I think @Joe Sefanelli provides an important part to your solution, and then mentions that you need to change INNER to LEFT joins.

So, you say you want to display all units in your units list. And, if there's no data for a unit, then display the unit and blanks for the data that doesn't exist.

Here's a possible solution. Change your FROM clause to the following:

FROM  [dbo].[Unit] u 
LEFT OUTER JOIN 
    (
    SELECT *
    FROM [dbo].[IUA] i
    JOIN [dbo].[Reports] r ON r.[Report_ID] = i.[Report_ID]
    JOIN [dbo].[State] s ON i.[St_ID] = s.[St_Id]
    WHERE r.[Account] = [dbo].[fn_Get_PortalUser_AccountNumber](11-11)
        AND r.[Rpt_Period] = '2126'
        AND r.[RptName] = 'tfd'
        AND r.[Type] = 'h'    
    ) ir ON ir.[Unit_ID] = u.[Unit_ID]
LEFT JOIN [dbo].[UnitType] ut ON u.[UnitType] = ut.[UnitType]
WHERE u.[Unit] IN (SELECT [VALUE] 
               FROM dbo.udf_GenerateVarcharTableFromStringList(@Units, ','))
;

With this change you will get a list of units that are in the @Units list. The left outer joins will include data associated with each unit, but will not exclude units if there is no associated data.

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135878

Based on the posted code, I think you're looking to blank out the columns from the UnitType table as that's the only one you're left-joining to. In that case use 

ISNULL(ut.[Description], '')  AS UnitType

Upvotes: 1

Rawheiser
Rawheiser

Reputation: 1228

Put your blank row select at the bottom of a union 

select x.JobName , x.Description
from MasterJobList x
where x.IsCycleJob = 1 

union all

select "" , "" 
from MasterJobList x
where not exists
    (
    select 1
    from MasterJobList x
    where x.IsCycleJob = 1 
    )

Upvotes: 4

tenfour
tenfour

Reputation: 36896

select a, b, c from t
if @@rowcount = 0
    select '' as a, '' as b, '' as c

But make sure you understand that '' may have a different datatype than columns a, b, and c.

Upvotes: 8

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55519

Try this -

IF NOT EXISTS ( SELECT 'x' FROM <TABLE> .... )
BEGIN
    -- Your logic goes here
END

Upvotes: 5

Related Questions