How to display a string when no row/s is returned

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to display a string when no row/s is returned

Author

Topic

k1t3
Starting Member

5 Posts

Posted - 2010-01-21 : 02:37:27

Example.

Table Z
+----+
| XY |
+----+
| 11 |
+----+
| 12 |
+----+

Select XY From Z where ID = 13

Instead of the blank table, I want a table like this to be returned

+-----+
| XY |
+-----+
| n/a |
+-----+

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 03:13:19

[code]
Select XY From Z where ID = 13
UNION ALL
SELECT 'n/a' WHERE NOT EXISTS (SELECT * FROM Z where ID = 13)
[/code]

Kristen
Test

22859 Posts

Posted - 2010-01-21 : 03:14:48

P.S. This won't work well if XY was an INT in the first SELECT statement, column datatypes in all SELECTS in the UNION need to have the same datatype.

Converting to VARCHAR is a bit of a mess - much better to do the data conversion in the application.

In which case maybe better to have the application take care of this instead ??

k1t3
Starting Member

5 Posts

Posted - 2010-01-21 : 03:55:17

Thank you very much!!

sudheepdivakar
Starting Member

5 Posts

Posted - 2010-01-21 : 23:39:24

try this, this queried db twice

if exists(Select XY From Z where ID = 13)
Select XY From Z where ID = 13
else
select 'n/a' XY From Z where ID = 13

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-01-22 : 14:05:41

Here are a couple other ways that might help:

DECLARE @Z TABLE (XY INT)

SELECT COALESCE
(
	(SELECT XY FROM @Z),
	0
)

SELECT COALESCE
(
	CAST((SELECT XY FROM @Z) AS VARCHAR(20)),
	'N/A'
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-23 : 07:32:00

quote:
Originally posted by k1t3

Example.

Table Z
+----+
| XY |
+----+
| 11 |
+----+
| 12 |
+----+

Select XY From Z where ID = 13

Instead of the blank table, I want a table like this to be returned

+-----+
| XY |
+-----+
| n/a |
+-----+

Was this requirement for displaying message in report? if it is, then SQL reporting tool has this property where it can show a default message when it doesnt have any data to show. its called NoRows property in table container.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources