January 10, 2011 at 1:15 pm
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
January 10, 2011 at 1:20 pm
What is the question?
January 10, 2011 at 1:24 pm
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)
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
January 10, 2011 at 1:25 pm
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.
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
January 10, 2011 at 1:30 pm
u need to utilize the case statement in ur query.
January 10, 2011 at 1:33 pm
--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
January 10, 2011 at 1:42 pm
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
January 10, 2011 at 2:08 pm
--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
January 12, 2011 at 12:06 pm
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
January 12, 2011 at 1:40 pm
s.CustodianID and s.FundAccountantID and s.BrokerID are probably integer.
You cannot compare between integer and varchar.
January 12, 2011 at 1:46 pm
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'
SELECT 'same'
ELSE
SELECT 'different'
SELECT @b-2 = 'B'
SELECT 'same'
ELSE
SELECT 'different'
January 12, 2011 at 2:15 pm
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'
SELECT 'same'
ELSE
SELECT 'different'
SELECT @b-2 = 'B'
SELECT 'same'
ELSE
SELECT 'different'
You are certainly right:-) I was neglected implicit conversion.
November 18, 2014 at 8:33 am
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