Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

T-SQL IF ELSE THEN Statement Expand / Collapse
Author
Message
Posted Monday, January 10, 2011 1:15 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 15, 2011 12:37 PM
Points: 3, 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....
Post #1045540
Posted Monday, January 10, 2011 1:20 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 10:10 AM
Points: 292, Visits: 1,615
What is the question?
Post #1045546
Posted Monday, January 10, 2011 1:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:16 PM
Points: 5,986, Visits: 6,931
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
Post #1045549
Posted Monday, January 10, 2011 1:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:16 PM
Points: 5,986, Visits: 6,931
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
Post #1045552
Posted Monday, January 10, 2011 1:30 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 7:23 AM
Points: 304, Visits: 457
u need to utilize the case statement in ur query.
Post #1045558
Posted Monday, January 10, 2011 1:33 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 7:23 AM
Points: 304, 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
Post #1045561
Posted Monday, January 10, 2011 1:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 5:49 AM
Points: 28, Visits: 416
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

Post #1045565
Posted Monday, January 10, 2011 2:08 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 7:23 AM
Points: 304, 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
Post #1045580
Posted Wednesday, January 12, 2011 12:06 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 15, 2011 12:37 PM
Points: 3, 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....
Post #1046698
Posted Wednesday, January 12, 2011 1:40 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 5:49 AM
Points: 28, Visits: 416
s.CustodianID and s.FundAccountantID and s.BrokerID are probably integer.
You cannot compare between integer and varchar.
Post #1046793
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse