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开发者_JAVA技巧 put a '' in every cell. How do I do this?

---

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.

---

Try this -

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

---

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 
    )

---

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.

---

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

---

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

---

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.