CODEWITHSUNDEEP
sqlsql-server
MeTa
MeTa

Reputation: 89

MSSQL Return 0 for null blank

select COALESCE([ItemSlotX],0) as [ItemSlotX],COALESCE([ItemSlotY], 0 ) as [ItemSlotY] from [PowerUP_Items] where [ItemIndex]=16 and [ItemGroup]=255

In my case, is no record.

How can I return 0 for ItemSlotX and 0 for ItemSlotY if there is no record found?

Upvotes: 0

Views: 133

Answers (3)

msi77
msi77

Reputation: 1632

If your query must return not more than one row, try this

select COALESCE(max([ItemSlotX]),0) as [ItemSlotX],COALESCE(max([ItemSlotY]), 0 ) as [ItemSlotY] 
from [PowerUP_Items] where [ItemIndex]=16 and [ItemGroup]=255

Upvotes: 0

Patrick Hofman
Patrick Hofman

Reputation: 156978

If you want the results, even when there is no matching row, use this:

select COALESCE([ItemSlotX],0) as [ItemSlotX],COALESCE([ItemSlotY], 0 ) as [ItemSlotY]
from (select null dummy ) d
left
outer
join [PowerUP_Items]
on   [ItemIndex]=16 and [ItemGroup]=255

Upvotes: 1

dean
dean

Reputation: 10098

This?

select ItemSlotX, ItemSlotY from PowerUP_Items where ItemIndex=16 and ItemGroup=255
if @@rowcount = 0
    select 0 as ItemSlotX, 0 as ItemSlotY

Or more general approach:

if exists (select * from PowerUP_Items where ItemIndex=16 and ItemGroup=255)
    select ItemSlotX, ItemSlotY from PowerUP_Items where ItemIndex=16 and ItemGroup=255
else
    select 0 as ItemSlotX, 0 as ItemSlotY

Upvotes: 1

Related Questions