How to Return Empty Row from SQL Server

Is it possible to return empty row from Sql Server?

Generally, if you must have an empty row returned..

If your original query is

select a,b,c from tbl

You can turn it into a subquery

select t.a,t.b,t.c
from (select 1 as adummy) a
left join (
    select a,b,c from tbl  -- original query
) t on 1=1

Which ensures the query will always have a rowcount of at least one.

How to return empty row from sql function if no record is found

If you need to return no rows when item exist but has no Pic_Path:

ALTER FUNCTION FunctionTEST (@itemID bigint)
RETURNS table AS RETURN 
(
    SELECT top 1 Pic_Path
    from PictureDetails
    WHERE PictureDetails.Item_ID = @itemID and Pic_Path is not NULL
)

Above function should be called with cross apply if you want no row for item without Pic_Path in result and with outer apply if you wish to inlcude such rows in result:

SELECT
    nctb.FirstName, nctb.LastName, nctb.EMail, nctb.ContactNo,
    addet.AD_ID, addet.AD_EXactAddress, addet.AD_PostingTime,
    addet.AD_Description, addet.AD_Title,
    idet.Item_ID, idet.Item_Price,
    r.R_Description,
    c.C_Name,
    gcat.GC_Description,
    scat.SC_Description,
    pdet.Pic_Path
FROM
    NewClientTB nctb
    join ADDetails addet on addet.AD_PosterID = nctb.UserID
    join ItemDetails idet on idet.AD_ID = addet.AD_ID
    join Regions r on r.R_ID = addet.AD_Region
    join Cities c on c.R_ID = addet.AD_Region AND c.C_ID = addet.AD_City
    join GCategories gcat on gcat.GC_ID = idet.Item_GCategory
    join SCategories scat on scat.SC_ID = idet.Item_SCategory
    cross apply dbo.FunctionTEST(addet.AD_ID) ft
    join PictureDetails pdet on pdet.Pic_Path = ft.Pic_Path
WHERE FREETEXT (addet.AD_Title, 'selling') AND FREETEXT (addet.AD_Description, 'selling')

Add empty row to query results if no results found

It's an old question, but i had the same problem.
Solution is really simple WITHOUT double select:

select top(1) WITH TIES * FROM (
select
id, category, 1 as orderdummy
from #test
where category = @category
union select 0, '', 2) ORDER BY orderdummy

by the "WITH TIES" you get ALL rows (all have a 1 as "orderdummy", so all are ties), or if there is no result, you get your defaultrow.

SELECT only the whole row where the column is empty in SQL

SELECT *
FROM your_table
WHERE [Closed Date] IS NULL OR LTRIM(RTRIM([Closed Date])) = ''

return empty row based on condition in sql server

Try this

select top 0 * from tablea
select top 0 * from tableb

That will return empty rows with the columns of TableA and TableB

Case when returning empty result rows

You can do this by two ways

1. Put Aggregate function in HAVING Clause

   HAVING Sum (Case when Year = @Year -2 then NNNRevenue Else 0 End) <> 0
   

2. Using Derive Table concept

   SELECT * 
   FROM(
     Paste your query here wihtout where clause
   )m
   Where RevenueP2 <> 0
   

If a query return empty row i need to get its as zero value,How can check it in Sql?

I suspect your best bet here is to use a LEFT OUTER join instead of an INNER JOIN, and use ISNULL; the LEFT OUTER means that you'll keep the row even when it doesn't match anything, and the ISNULL lets you choose a value to use in that scenario; so:

Update TABLE_A
Set  TABLE_A.Form_8 =(Case When TABLE_B.Form_Type ='8' Then  ISNULL(TABLE_B.Invoice_NO, 0) End)
From TABLE_A
Left Outer Join (Select Max(Invoice_NO) as Invoice_NO,Form_Type, Counter_Name From Sales_Master
Group By Counter_Name,Form_Type
) TABLE_B on  TABLE_A.Counter_Name = TABLE_B.Counter_Name and 

TABLE_B.Form_Type ='8'

Insert empty rows in select result if number of rows is under a certain amount

One solution would be to create a "numbers table" as either a CTE or derived table, for numbers 1-10, and then make sure that your query has some kind of line number. Then just LEFT OUTER JOIN to your query.

Something like this:

WITH NUMBERS AS
(
SELECT 1 LINE
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
UNION ALL
SELECT 10
)

SELECT YOURTABLE.* 
    FROM NUMBERS
        LEFT OUTER JOIN YOURTABLE
            ON YOURTABLE.LINE = NUMBERS.LINE

There might be something slightly more elegant but that should work...

---

Related Topics