Setting variables with 'if exists'

  • First of all hi everyone..Happy New Year. Haven't logged in since last year!

    OK, today's dilema:

    In a stored procedure, I am looking to set variables after I select out info with an "if exists" statement....

    if @Mode = 'WEB'

    begin

    if exists

    (

    select

    pro.ProviderEntityID,

    pro.Alias

    from

    Migration.dbo.util_DH_Providers pro

    where pro.ProviderEntityID = @PersonID

    )

    set @EntityType = 'Professional'

    else

    set @EntityType = 'Customer'

    I also need to set these variables

    set @UserNamePro = pro.Alias

    set @EntityID = pro.ProviderEntityID

    but it says that the pro prefix doesn't match the query alias....what am I doing wrong and where should these be set at???

    Thanks everyone!!:hehe:


    Thank you!!,

    Angelindiego

  • Why not try to dosomething like ...

    select @UserNamePro = pro.Alias, @EntityID = pro.ProviderEntityID

    from

    Migration.dbo.util_DH_Providers pro

    where pro.ProviderEntityID = @PersonID

    if @UserNamePro is Null

    set @EntityType = 'Professional'

    else

    set @EntityType = 'Customer'

    does that work? you'd only be doing one select instead of a couple, which is what I think you are trying to avoid.

  • thank you Chris!

    I changed things around (realizing that if exists brings back a T or F)

    and had this in place:

    if @Mode = 'WEB'

    begin

    select

    @EntityID = pro.ProviderEntityID,

    @UserNamePro = pro.Alias

    from

    Migration.dbo.util_DH_Providers pro

    where pro.ProviderEntityID = @PersonID

    if @@RowCount > 0

    set @EntityType = 'Professional'

    else

    set @EntityType = 'Customer'

    you just confirmed what I was doing, so thank you again!!


    Thank you!!,

    Angelindiego

  • np, glad to help

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply