Single SQL statement to select row, but only if there is exactly one matching row

sql, sql-server

Solution

This will work and won't require you to do more than one single SELECT statement. You will have a lot of freedom on changing the query thereafter, should you need to, without breaking anything.

SELECT @WidgetId = WidgetId
    FROM Widgets
    WHERE WidgetName = 'foo'

IF @@ROWCOUNT = 1
BEGIN
    --Here you are certain that there's only one matching row found
END
ELSE
BEGIN
    --Here zero or more than one records were found
END

Problem

Consider the following SQL: ``` SELECT @Count = COUNT(*) FROM Widgets WHERE WidgetName = 'foo' IF ( @Count = 1 ) BEGIN SELECT @WidgetId = WidgetId FROM Widgets WHERE WidgetName = 'foo' END ``` It effectively sets the `@WidgetId` value, but only if there is exactly one matching row that satisfies the query. The query above is trivial, but consider the case where the WHERE clause is rather expensive. Or if the WHERE clause is more complicated than a single, complete column. (`WHERE WidgetName LIKE '%foo%'`) Is there a way to do a single query to assign the value to the parameter, but only when exactly one row matches, without repeating the WHERE clause?

Original source