Return a value if record doesn not exist

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

General SQL Server Forums

New to SQL Server Programming

Return a value if record doesn not exist

Author

Topic

gooner_chef
Starting Member

1 Post

Posted - 2009-06-17 : 15:12:01

Hi,

Is is it possible to retun a value from a select statement if the record does not exist in the database.

For example,

SELECT price FROM products
WHERE productname = 'bob'

How can I return 0 if there is no product called 'bob'?

Thanks!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-17 : 15:17:22

IF EXISTS(SELECT price FROM products
WHERE productname = 'bob')
BEGIN
SELECT price
FROM products
WHERE productname = 'bob'
END
ELSE SELECT 0

Jim

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-17 : 15:38:52

There are a couple ways. One way is with the UNION operator:

SELECT 
	MAX(price)
FROM
(
	SELECT price FROM products
	WHERE productname = 'bob'
	
	UNION ALL 

	SELECT 0
) AS T

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 15:45:33

A simple COALESCE may do.

DECLARE	@Sample TABLE
	(
		ProductName VARCHAR(20),
		Price MONEY
	)

INSERT	@Sample
SELECT	'Peso', 100

SELECT	COALESCE(MIN(Price), 0)
FROM	@Sample
WHERE	ProductName = 'Peso'

SELECT	COALESCE(MIN(Price), 0)
FROM	@Sample
WHERE	ProductName = 'SQLTeam'

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources