Single query with value even no records return

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)

Single query with value even no records return

Author

Topic

ryanlcs
Yak Posting Veteran

62 Posts

Posted - 2010-07-07 : 04:27:34

I have a simple query

Select Name From Table1 Where ID = "1"

How can I have a value of name such as 'MyName' even the record return is 0.

It is something like this, the following value return is 'MyName' if the value is null, where record count is 1. But in the above query, I want the value of 'MyName' even when the record count is 0.

Select IsNull(Name,'MyName') From Table1 Where ID = "1"

Any idea?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-07-07 : 04:41:38

One way:

SELECT TOP 1 Name 
FROM (
  Select Name, ID From Table1 Where ID = "1"
  UNION ALL
  SELECT 'MyName', -1
  ) as dt
ORDER BY ID DESC

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-07 : 04:51:26

Or simply make use of an aggregate function?

DECLARE	@Sample TABLE
	(
		ID INT,
		Name VARCHAR(20)
	)

INSERT	@Sample
VALUES	(1, 'Peso'),
	(2, 'MVP')


SELECT	COALESCE(MIN(Name), 'MyName')
FROM	@Sample
WHERE	ID = 1

SELECT	COALESCE(MIN(Name), 'MyName')
FROM	@Sample
WHERE	ID = 3

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources