Select statement to return constant when no record

Question

I am have a table with data and now i need to return zero in select statement if there is no records in table for example. I need to use it in Stored Procedure.

-- If no records exists in below select statement
SELECT ID,Text,Date FROM tblData WHERE ID = 12

IF (@@ROWCOUNT = 0)
BEGIN
    SELECT -5 AS ID
END

Output:

ID  Text  Date


ID
-5

Expected output

ID
-5

Answer 1

If you want to return 1 row even when there is no match, you can use aggregation:

SELECT (CASE WHEN COUNT(*) = 0 THEN -5 ELSE MAX(ID) END) as ID
FROM tblData
WHERE ID = 12;

Answer 2

For a single scalar value you could use something like;

SELECT ISNULL((SELECT ID FROM tblData WHERE ID = 12), 0) as ID

Rhys

Answer 3

I always use an Exists statment.

if exists(SELECT ID FROM tblData WHERE ID = 12)
    select 0 as RowsExist
else
    select 1 as RowsExist

Answer 4

SELECT (CASE WHEN Ta.ID IS NULL THEN TBL.ID 
ELSE Ta.ID END) AS ID,Ta.Text,Ta.Date
FROM (VALUES(-5)) AS TBL(ID)
LEFT JOIN
(
   SELECT ID,Text,Date FROM tblData WHERE ID = 12
)
AS Ta ON Ta.ID = Ta.ID