SQLTeam.com | Weblogs | Forums

Table Empty, Return Empty Flag


#1

Hi,

Is there a better way of writing this query, with ONE WHOLE statement because it needs to go into an inline table.

If the table is empty, it will return "N/A" in all fields or just one field. If not empty, it will return records without the "N/A"

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
BEGIN
	DROP TABLE #TempTable
END
GO

CREATE TABLE #TempTable
(
	  MyID				INT IDENTITY(1,1)
	, First_Value		INT
)

INSERT INTO #TempTable VALUES (11)

DECLARE @Empty		AS BIT
SET @Empty = (SELECT COUNT(*) FROM #TempTable)
SELECT @Empty

IF @Empty = 0
	BEGIN
		SELECT 'N/A'
	END
ELSE
	BEGIN
		SELECT * FROM #TempTable
	END

#2

Found this: http://sqlknowledgebank.blogspot.com/2016/03/default-value-of-select-when-no-records.html

I think this did it:

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
BEGIN
	DROP TABLE #TempTable
END
GO

CREATE TABLE #TempTable
(
	  MyID				INT IDENTITY(1,1)
	, First_Value		INT
)

INSERT INTO #TempTable VALUES (11)


SELECT aT1.* FROM #TempTable aT1
WHERE aT1.MyID IN
(
	SELECT COALESCE(aT2.MyID, aT3.MyID) AS MyID
	FROM #TempTable aT2
	RIGHT OUTER JOIN (SELECT NULL AS MyID) aT3 ON 1 = 1
)