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.
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_FattWHERE UP_ID = 829PRINT @kbecause 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 INTSET @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. |
|
|
|
|
|