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

Question

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.)

Answer 1

Here you go:

SELECT ('ABC-' + 
    ISNULL(
      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 
    , 1) 
  FROM [STUFF]
  WHERE [FK_DATA] = '12345'

The sub-query is placed inside an ISNULL function. By doing this, you are saying that if the sub-query returns a value of NULL, replace it with a value of 1.

If rows are returned, the result will be as you already have it, but if no rows are returned, the result will be 1.