jeudi 21 juin 2018

Replace a isNull with If Exists

I have a stored procedure where it can return no row, in that case I would like to put 'Other' as a value. My procedure set the values of my column '@col', I tried to use ISNULL but it is wrong

set '+@col+' = isnull(p.par_name, ''Other'')
from #CALENDAR cal
inner join #WEBSITES w on w.par_id = cal.par_id and
    (
        w.bur_id = cal.bur_id
        or
        (w.date = cal.Date or (w.[date] is null and cal.Date is null))
    )
left join partner p on p.par_id = isnull(w.par_id,-1) AND tpa_id = 3 -- partner de type advertiser
where website_id ='+convert(varchar(3), @wid)

I tried to put a IF EXIST like so, it didn't work :

set '+@col+' = if exists (p.par_name
from #CALENDAR cal
inner join #WEBSITES w on w.par_id = cal.par_id and
    (
        w.bur_id = cal.bur_id
        or
        (w.date = cal.Date or (w.[date] is null and cal.Date is null))
    )
left join partner p on p.par_id = isnull(w.par_id,-1) AND tpa_id = 3 -- partner de type advertiser
where website_id ='+convert(varchar(3), @wid)+') else ''Other'' '

To simplify the query what i do is (mix sql and c#):

if(w.par_id = null)
 select p.par_name from partner p where p.par_id = -1 <= will return nothing
THEN if(p.par_name is EMPTY) THEN p.par_name='Other'

Which will return no row, in that case I want to put 'Other'

Aucun commentaire:

Enregistrer un commentaire