T-SQL IF ELSE THEN Statement

  • USE [cashNotification]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER PROCEDURE [dbo].[Sel_DCF_GetContactsByRefType]

    @ContactType varchar (50),

    @ContactSubType varchar (50),

    @ID varchar (50)

    AS

    BEGIN

    SET NOCOUNT ON;

    SELECT DISTINCT

    c.ContactID,

    c.ContactName,

    c.PhoneNum,

    c.Fax,

    c.PersonalEmailAddress,

    c.CompanyPhoneNum,

    c.GroupEmailAddress,

    c.Address1,

    c.Address2,

    c.Address3,

    c.Address4,

    c.City,

    c.State,

    c.Zip,

    c.CountryCode

    FROM

    tblDCF_SubAccounts s,

    tblDCF_Contacts_Accounts a,

    tblDCF_Contacts c

    WHERE

    a.ContactID = c.ContactID AND

    a.ContactType = @ContactType AND

    a.ContactSubType = @ContactSubType AND

    s.HiNetMinor = a.HiNetMinor

    -- s.BrokerID = @ID

    IF @ContactType = 'Broker'

    Begin

    SETs.BrokerID = @ID

    END

    ELSE IF @ContactType = 'FundAcct' --THEN

    Begin

    SET s.FundAccountantID = @ID

    END

    ELSE IF @ContactType = 'Custodian' --THEN

    Begin

    SETs.CustodianID = @ID

    END

    END IF

    Order By c.ContactName

    --Grant execute on Sel_DCF_GetContactsByRefType to public

    END

    Heather B....
  • What is the question?

  • You can't use IF mid-query. IF is used for proc control, not mid-statement decisions, you want CASE for that usually, but neither here...

    So this:

    F @ContactType = 'Broker'

    Begin

    SET s.BrokerID = @ID

    END

    ELSE IF @ContactType = 'FundAcct' --THEN

    Begin

    SET s.FundAccountantID = @ID

    END

    ELSE IF @ContactType = 'Custodian' --THEN

    Begin

    SET s.CustodianID = @ID

    END

    END IF

    can't be used except BEFORE the query, and not for what you're looking for.

    Also, you declare @ID in the parameters list, which means people will expect to set it, and then override it.

    What you'll probably want to do is remove @ID from that list, and then use a simple DECLARE in the body.

    Next, you'll need to do something along these lines:

    SELECT @ID = CASE @contacttype WHEN 'Broker' THEN s.BrokerID WHEN 'FundAcct' THEN s.FundAccountantID' WHEN 'Custodian' THEN s.CustodianID ELSE NULL END

    FROM ... (rest of statement)


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Lamprey13 (1/10/2011)


    What is the question?

    It was in the subtitle on the main page, it doesn't show here, unfortunately, and he didn't realize he'd need to repeat it in the first post.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • u need to utilize the case statement in ur query.

  • --somehting like this

    SELECT DISTINCT

    c.ContactID,

    c.ContactName,

    c.PhoneNum,

    c.Fax,

    c.PersonalEmailAddress,

    c.CompanyPhoneNum,

    c.GroupEmailAddress,

    c.Address1,

    c.Address2,

    c.Address3,

    c.Address4,

    c.City,

    c.State,

    c.Zip,

    c.CountryCode

    FROM

    tblDCF_SubAccounts s,

    tblDCF_Contacts_Accounts a,

    tblDCF_Contacts c

    WHERE

    a.ContactID = c.ContactID AND

    a.ContactType = @ContactType AND

    a.ContactSubType = @ContactSubType AND

    s.HiNetMinor = a.HiNetMinor

    -- s.BrokerID = @ID

    case when @ContactType = 'Broker'

    then s.BrokerID

    when @ContactType = 'FundAcct'

    THEN s.FundAccountantID

    when @ContactType = 'Custodian'

    THEN s.CustodianID

    else 0 end = @ID

    Order By c.ContactName

  • It is not nice, but it can be fast.

    IF @ContactType = 'Broker'

    Begin

    SELECT DISTINCT

    c.ContactID,

    c.ContactName,

    c.PhoneNum,

    c.Fax,

    c.PersonalEmailAddress,

    c.CompanyPhoneNum,

    c.GroupEmailAddress,

    c.Address1,

    c.Address2,

    c.Address3,

    c.Address4,

    c.City,

    c.State,

    c.Zip,

    c.CountryCode

    FROM

    tblDCF_SubAccounts s inner join

    tblDCF_Contacts_Accounts a on s.HiNetMinor = a.HiNetMinor inner join

    tblDCF_Contacts c on a.ContactID = c.ContactID

    WHERE

    a.ContactType = @ContactType

    AND a.ContactSubType = @ContactSubType

    AND s.BrokerID = @ID

    Order By c.ContactName

    END

    ELSE IF @ContactType = 'FundAcct'

    Begin

    SELECT DISTINCT

    /* the same query */

    AND s.FundAccountantID = @ID

    END

    ELSE IF @ContactType = 'Custodian'

    Begin

    SELECT DISTINCT

    /* the same query */

    AND s.CustodianID = @ID

    END

  • --sorry. i forgot an "and" in there

    --somehting like this

    SELECT DISTINCT

    c.ContactID,

    c.ContactName,

    c.PhoneNum,

    c.Fax,

    c.PersonalEmailAddress,

    c.CompanyPhoneNum,

    c.GroupEmailAddress,

    c.Address1,

    c.Address2,

    c.Address3,

    c.Address4,

    c.City,

    c.State,

    c.Zip,

    c.CountryCode

    FROM

    tblDCF_SubAccounts s,

    tblDCF_Contacts_Accounts a,

    tblDCF_Contacts c

    WHERE

    a.ContactID = c.ContactID AND

    a.ContactType = @ContactType AND

    a.ContactSubType = @ContactSubType AND

    s.HiNetMinor = a.HiNetMinor and

    case when @ContactType = 'Broker'

    then s.BrokerID

    when @ContactType = 'FundAcct'

    THEN s.FundAccountantID

    when @ContactType = 'Custodian'

    THEN s.CustodianID

    else 0 end = @ID

    Order By c.ContactName

  • Thanks for your help. I changed the code, however I ran the stored proc and get an error.

    Parameters Used: 'Custodian', 'Escalation', 'MLCO'

    The error message displayed is:

    Conversion failed when converting the varchar value 'MLCO' to data type int

    What does this code mean? "else 0 end = @ID" Could this be the issue? Thanks.

    Heather

    Heather B....
  • s.CustodianID and s.FundAccountantID and s.BrokerID are probably integer.

    You cannot compare between integer and varchar.

  • nekonecny (1/12/2011)


    s.CustodianID and s.FundAccountantID and s.BrokerID are probably integer.

    You cannot compare between integer and varchar.

    You can compare integer and varchar in some cases. An implicit conversion may occur, but it is not what you want to do. You want to explicitly cast one as the other to ensure a valid comparison.

    First one works, second doesn't:

    DECLARE @a int, @b-2 varchar(2)

    SELECT @a = 1

    SELECT @b-2 = '1'

    IF @a = @b-2

    SELECT 'same'

    ELSE

    SELECT 'different'

    SELECT @b-2 = 'B'

    IF @a = @b-2

    SELECT 'same'

    ELSE

    SELECT 'different'

  • Steve Jones - SSC Editor (1/12/2011)


    nekonecny (1/12/2011)


    s.CustodianID and s.FundAccountantID and s.BrokerID are probably integer.

    You cannot compare between integer and varchar.

    You can compare integer and varchar in some cases. An implicit conversion may occur, but it is not what you want to do. You want to explicitly cast one as the other to ensure a valid comparison.

    First one works, second doesn't:

    DECLARE @a int, @b-2 varchar(2)

    SELECT @a = 1

    SELECT @b-2 = '1'

    IF @a = @b-2

    SELECT 'same'

    ELSE

    SELECT 'different'

    SELECT @b-2 = 'B'

    IF @a = @b-2

    SELECT 'same'

    ELSE

    SELECT 'different'

    You are certainly right:-) I was neglected implicit conversion.

  • Nice example TY

Viewing 13 posts - 1 through 12 (of 12 total)

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