How to get this SQL to return 1 if no rows are found
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'
The table [STUFF]
contains a column [ID]
with values such as "ABC-1", "ABC-2". But the WHERE clause can result in 0 rows being returned.
The request gets the next ABS increment, for example. "ABC-3". Except when there are no matching lines. Then I get no results.
This is part of a subquery, so I need to return ABC-1 instead of nothing. That is, the inner must return 1. (My outer query then inserts this newest increment.)
source to share
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 subquery is placed inside the ISNULL function. By doing this, you are saying that if the subquery returns NULL, replace it with 1.
If strings are returned, the result will be the same as you already have, but if no strings are returned, the result will be 1.
source to share
You can do this depending on the context, although it's not overly elegant
SELECT TOP 1 next_id
FROM (
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'
UNION ALL
SELECT 'ABC-1' AS next_id
) AS next_id
source to share
Use this @temptable
in your subquery or anywhere
declare @temptable as table (next_id int)
insert into @temptable
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'
if not exists (select next_id from @temptable)
select 'ABC-1' as next_id
else
select * from @temptable
source to share