Return 0 if record does not exists

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

Transact-SQL (2012)

Return 0 if record does not exists

Author

Topic

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-01-30 : 10:02:29

Hello all,
having this query:

DECLARE @k DECIMAL(18,10)
SELECT @k = ISNULL(value,0) FROM app.T_Fatt
WHERE UP_ID = 829
PRINT @k

because the record with UP_ID how can I force to return 0 instead of nothing?

Luigi

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-30 : 10:04:27

Initialize it to zero.

DECLARE @k DECIMAL(18,10) = 0;

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-01-30 : 12:12:31

James, solution will just fine. However, we use a slightly different pattern to help avoid situations where a query could return more than one row (thus assigning a random value to the variable). With this pattern SQL will error if more than one vaue is returned and it is mroe ovbious what you are trying to achomplish (i.e.: you want a zero result and not a null):

DECLARE @Foo TABLE (ID INT, Val VARCHAR(20))

DECLARE @Bar INT


SET @Bar = COALESCE
	(
		(
			SELECT ID
			FROM @Foo
			WHERE Val = 'Test'
		)
		, 0
	)

SELECT @Bar

Ciupaz
Posting Yak Master

232 Posts

Posted - 2013-01-30 : 14:14:31

They're both useful solutions.
Thank you all.

Luigi

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-30 : 18:46:15

Thanks Lamprey! Ciupaz, if there is the possibility that there would be more than one row for a given UP_ID, use Lamprey's suggestion - especially so if for some of those rows the value column happens to have NULLs.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources