How do I get this SQL to return a 1 if no rows are found

Viewed 65

SQL Server 2008

SELECT ('ABC-' + 
    MAX(CAST(
        (SUBSTRING([ID], CASE CHARINDEX('-', [ID])
            WHEN 0
                THEN LEN([ID]) + 1            
            ELSE CHARINDEX('-', [ID]) + 1
            END, 1000)+1)
            AS VARCHAR)
            )) AS next_id      
  FROM [STUFF]
  WHERE [FK_DATA] = '12345'

Table [STUFF] contains column [ID] with values like "ABC-1", "ABC-2". But the WHERE clause may result in 0 rows returned.

The query gets the next increment of the ABS's, e.g. "ABC-3". Except when there are no matching rows. Then I don't get any results.

It's part of a sub-query, so I need it to return 'ABC-1' instead of nothing. I.e. the inner part has to return a 1. (My outer query then inserts this newest increment.)

4 Answers
Related