Return default entry from SQL query

Question

I have a sql query that I run for a sql server database, for example.

SELECT * FROM MyTable WHERE Id = 2 

This may return multiple records or may not return them. If it does not return anything, I would like to modify my sql query to return the default entry, is this possible, and if so, how? If records are returned, the default record should not be returned. I cannot update the data, so for this I will need to modify the SQL query.

Answer 1

Another way (you get an empty initial rowset);

 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 watch this post. This is similar to what you ask for. Returns value if rows are not found. SQL

I hope it helps you find the right path.

Answer 4

if does not exist ( SELECT top 1 * FROM mytable WHERE id = 2 )

 select * from mytable where id= 'whatever_the_default_id_is' 

else

 select * from mytable where id = 2 

Answer 5

If you need to return whole rows of data (and not just one column), and you need to create one SQL query, do the following:

Left table join table by default for single row table

 select coalesce(a.col1, d.col1) as col1, coalesce(a.col2, d.col2) as col2, ... from ( -- your defaults record select default1 as col1, default2 as col2, ...) as d left join actual as a on ((1 = 1) /* or any actual table "where" conditions */) 

Answer 6

The query should return the same number of fields, so you should not do SELECT * FROM , but SELECT value FROM if you want to return the default value.

With this in mind

 SELECT value FROM MyTable WHERE Id = 2 UNION SELECT CASE (SELECT count(*) FROM MyTable WHERE Id = 2) WHEN 0 THEN 'defaultvalue' END