Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL IF ELSE THEN Statement


T-SQL IF ELSE THEN Statement

Author
Message
habby0123
habby0123
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 17
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
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

Order By c.ContactName

--Grant execute on Sel_DCF_GetContactsByRefType to public
END

Heather B....
Lamprey13
Lamprey13
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 Visits: 1657
What is the question?
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5697 Visits: 7660
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5697 Visits: 7660
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
BaldingLoopMan
BaldingLoopMan
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 457
u need to utilize the case statement in ur query.
BaldingLoopMan
BaldingLoopMan
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 457
--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
nekonecny
nekonecny
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 493
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


BaldingLoopMan
BaldingLoopMan
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 457
--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
habby0123
habby0123
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 17
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....
nekonecny
nekonecny
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 493
s.CustodianID and s.FundAccountantID and s.BrokerID are probably integer.
You cannot compare between integer and varchar.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search