Conditional WHERE clause

Hi,

[SQL 2005 Express]

I would like a DropDownList to be populated differently depending on the selected value in a FormView.

If the FormView's selected value (CompanyID) is 2, then the DropDownList should show all Advisers from the relevant Company. Otherwise, the DropDownList should show all Advisers from the relevant Company where the TypeID field is 3.

Here is the SQL for case 1:

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
(CompanyID = @.CompanyID).

Here's the SQL for case 2:

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
(CompanyID = @.CompanyID) AND
(TypeID = 3).

Here's my best (failed) attempt to get what I want:

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
IF @.CompanyID = 2 THEN
BEGIN
(CompanyID = @.CompanyID)
END
ELSE
BEGIN
(CompanyID = @.CompanyID) AND
(TypeID = 3)
END

I've also tried:

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
CASE @.CompanyID
WHEN 2 THEN (CompanyID = @.CompanyID)
ELSE (CompanyID = @.CompanyID) AND
(TypeID = 3)
END

and

SELECT
AdviserID,
AdviserName
FROM
Advisers
WHERE
CASE WHEN (@.CompanyID = 2) THEN (CompanyID = @.CompanyID)
ELSE (CompanyID = @.CompanyID) AND (TypeID = 3)
END

I'd be very grateul to know (a) what the correct syntax for this is and (b) if it can be achieved using a parametised query, rather than a stored procedure.

Thanks very much.

Regards

Gary

Gary,

I think you are trying to construct a select based on the values selected in some control in your web form. (correct me if i am wrong) while going through your issue, i think the following solution should work -

Generate a dynamic where clause -

declare @.SQLSelect varchar(4000),

@.SQLWhere varchar(2000)

set @.SQLSelect = 'SELECT
AdviserID,
AdviserName
FROM
Advisers '

If @.CompanyID = 2

Begin

@.SQLWhere = ' Where CompanyID = ' + @.CompanyID

End

Else

Begin

@.SQLWhere = ' Where CompanyID = ' + @.CompanyID + ' and TypeID = 3'
End

set @.SQLSelect = @.SQLSelect + @.SQLWhere

Execute @.SQLSelect

I think this will help you to think further, if didn't solve your problem.

Ash

|||

Thanks, Ash.

I suspect that I'm too much of a novice to know what to do with what you've posted.

I've tried executing it as a Query and as a Stored Procedure - with no success. I ended up simplifying it to the same WHERE clause, with no better results:

Here is the simplified query:

DECLARE @.SQLSelect varchar(4000), @.SQLWhere varchar(2000)
SET @.SQLSelect = 'SELECT AdviserID, AdviserName FROM Advisers '
IF @.AdviserCompanyID = 2
BEGIN
@.SQLWhere = ' Where AdviserCompanyID = ' + @.AdviserCompanyID
END
ELSE
BEGIN
@.SQLWhere = ' Where AdviserCompanyID = ' + @.AdviserCompanyID
END
SET @.SQLSelect = @.SQLSelect + @.SQLWhere
EXECUTE @.SQLSelect

This generated: "Must Declare the scalar variable @.AdviserCompany"

So, I changed the first line to:

DECLARE @.SQLSelect varchar(4000), @.SQLWhere varchar(2000), @.AdviserCompanyID int

This generated:"Incorrect syntax near '@.SQLWhere'"

No joy after much fiddling. I then tried to create a Stored Procedure - with similar success levels.

What am I not getting?

Thanks very much for your help.

regards

Gary

|||

Gary:

You are not the only one. It took me hours to come to this solution.

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:mytestConnectionString %>"SelectCommand="SELECT [AdviserID], [AdviserName] FROM [Adviser]

WHERE CompanyID = CASE @.CompanyID WHEN 2 THEN 2 ELSE @.CompanyID END AND

TypeID = CASE @.CompanyID WHEN 2 THEN TypeID else 3 END"

><SelectParameters><asp:ControlParameterControlID="FormView1$companyIDtxtbox"Name="CompanyID"Type="Int32"/></SelectParameters></asp:SqlDataSource>|||

Excellent, Limno - thanks very much! Works a charm...

(Still keen to find out if I was doing something stupid to prevent the Previous idea from working...Anyone?)

Regards

Gary

|||

Hi Guys,

I would like to apply Limnon's solution to the WHERE clause in the following select statement:

SELECT
FIInvestments.InvestmentID,
Accounts.AccountName + ' - ' + CAST(Accounts.AccountNumber AS varchar(20)) + CONVERT
varchar, FIInvestments.InvestmentDate, 3) + ': ' + ' ($' + LEFT (CAST(FIInvestments.Amount AS varchar),
LEN(CAST(FIInvestments.Amount AS varchar)) - 3) + ' for ' + CAST(FIInvestments.Term AS varchar(3)) + '
months)' AS Investment
FROM
FIInvestments INNER JOIN
Accounts ON FIInvestments.AccountID = Accounts.AccountID
WHERE (FIInvestments.FundID = @.FundID) AND (NOT EXISTS
(SELECT PaymentID, CommPaymentID, Date, InvestmentID, Amount, Notes
FROM FICommPayments
WHERE (InvestmentID = FIInvestments.InvestmentID)))

(In other words: I want all the Investments with the selected FundID, unless commission has already been paid on them, as evidenced by payment records in the FICommPayments table. This is so I can allocate commissionpayments to them).

However, there is one Fund/FundID (FundID = 141) which pays commission in dribs and drabs, so if that Fund is selected, I wantall the investments within that Fund - not just the ones that haven't had commission paid against them yet.

So, here are my two WHERE statements:

WHERE (FIIinvestments.FundID = @.FundID)

and

WHERE (FIInvestments.FundID = @.FundID) AND (NOT EXISTS
(SELECT PaymentID, CommPaymentID, Date, InvestmentID, Amount, Notes
FROM FICommPayments
WHERE (InvestmentID = FIInvestments.InvestmentID)))

And here's my best attempt at a conditional WHERE statement so far:

WHERE CASE @.FundID WHEN 141 THEN (FIInvestments.FundID = @.FundID) ELSE (FIInvestments.FundID = @.FundID) AND (NOT EXISTS (SELECT PaymentID, CommPaymentID, Date, InvestmentID, Amount, Notes FROM FICommPayments WHERE (InvestmentID = FIInvestments.InvestmentID))) END

The error messages this generates are:

Incorrect syntax near '='.
Incorrect syntax near ')'.

I also tried, with little hope:

WHERE (FIInvestments.FundID = @.FundID) CASE @.FundID WHEN NOT 141 THEN (NOT EXISTS
(SELECT PaymentID, CommPaymentID, Date, InvestmentID, Amount, Notes
FROM FICommPayments
WHERE (InvestmentID = FIInvestments.InvestmentID)))END

This generated:

Incorrect syntax near the keyword 'NOT'.
Incorrect syntax near ')'

Perhaps I should revert to Ash's solution - but I got stuck on that one, too!

Thanks for the help.

Regards

Gary

|||

Hello:

I don't know why your approaches did not work. You may post that question again if you want an answer. Instead, you can split your condition in two separate parts, then use UNION OR UNION ALL (with possible duplicates) to combine the results.

I used a simplified version of your tables to test the following script, it works as to my understanding. But you may need to tweak it for your real case.

SELECT FIInvestments.InvestmentID, FIInvestments.FUNDID
FROM FIInvestments WHERE FundID<>141 AND FundID=@.FundID AND FIInvestments.FundID NOT IN (SELECT FICommPayments.FUNDID
FROM FICommPayments INNER JOIN FIInvestments ON FICommPayments.InvestmentID = FIInvestments.InvestmentID)
UNION ALL
SELECT FIInvestments.InvestmentID, FIInvestments.FUNDID
FROM FIInvestments
WHERE FundID=141 ANDFundID=@.FundID

|||

Excellent, Limon!

I started off thinking that your suggestion wasn't exactly what I'm after in this case (because I need one list for case 141 and the other for every other case, so the UNION ALL didn't seem like what I was after until it dawned on me what you're doing - very sneaky!).

I've been looking for UNION / UNION ALL for other reasons, so I get a double hit out of this one. Thanks very much - you're making my day on a number of fronts at the moment (including on the other thread)!

If this keeps up much longer, I'll have to put you on a retainer. In fact, I think that the guru's amongst you should work out an easy way of allowing novices like me to secure a commercial agreement/service with you guys in addition to this freebie one. I know that it exposes the community to abuse, but I'm sure there must be a way of doing it... I'll keep thinking and ewxperiencing and come up with something over the next month or two.

Regards

Gary