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