SQL if no rows are returned do this....

Question

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?

Answer 1

Try this -

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

Answer 2

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.

Answer 3

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 
    )

Answer 4

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

Answer 5

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.