问题:

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

回答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;

回答2:

I always use an Exists statment.

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

回答3:

For a single scalar value you could use something like;

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

Rhys

回答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