return constant values even if query comes up empt

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 2005 Forums

Transact-SQL (2005)

return constant values even if query comes up empt

Author

Topic

roundy72
Starting Member

2 Posts

Posted - 2009-09-19 : 23:59:15

hi all,

so i have a problem i cannot solve. i run a query that contains a "constant" (hardcoded) variable, and if the other variables from the db table come up blank, i still want a row returned (with null / blank for all db columns, but the hardcoded variables to show)

for example:

select name, address, '12345' as zip from mytable
where name='my_name'

if no entry exists in mytable where name='my_name', i get 0 rows.
but i want at least 1 row, showing (blank) for name, (blank) for address, and 12345 for zip.

i tried something like:

select isnull(name, ''), isnull(address, ''), '12345' as zip from mytablewhere name='my_name'

but still 0 rows returned.

can someone please help?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-09-20 : 02:21:05

select top (1) isnull(name, ''), isnull(address, ''), '12345' as zip from mytablewhere name = 'my_name'
union all
select '', '', '12345'
order by name desc

N 56°04'39.26"
E 12°55'05.63"

Andreas
Starting Member

11 Posts

Posted - 2009-09-20 : 10:34:15

I think Peso meant this?

select top(1) *
from
(
   select name, address, zip from mytable where name='my_name'
   union all
   select '', '', '12345'
) t
order by name desc

Otherwise the row with the empty name and address will always be appended.

Another suggestion, if your query could return more than one row and you want to see them all, is this:

if exists(select 1
         from mytable
         where name='my_name')
   select name, address, zip from mytable where name='my_name'
else
   select '' name, '' address, '12345' zip

roundy72
Starting Member

2 Posts

Posted - 2009-09-20 : 11:58:58

that is beautiful, thank you so much guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources