return a default record from a sql query

Question

I have a sql query that I run against a sql server database eg.

SELECT * FROM MyTable WHERE Id = 2

This may return a number of records or may return none. If it returns none, I would like to alter my sql query to return a default record, is this possible and if so, how? If records are returned, the default record should not be returned. I cannot update the data so will need to alter the sql query for this.

Answer 1

Another way (you would get an empty initial rowset returned);

SELECT * FROM MyTable WHERE Id = 2
IF (@@ROWCOUNT = 0)
   SELECT ...

Answer 2

SELECT TOP 1 * FROM (
SELECT ID,1 as Flag FROM MyTable WHERE Id = 2
UNION ALL
SELECT 1,2
) qry
ORDER BY qry.Flag ASC

Answer 3

You can have a look to this post. It is similar to what you are asking Return a value if no rows are found SQL

I hope that it can guide you to the correct path.