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

conditional dynamic SQL in stored procedure, not returning any result

Created a stored procedure which returns Selected table from database.

I pass variables,according to conditions

For some reason it is not returning any result for any condition

Stored Procedure

ALTER PROCEDUREdbo.StoredProcedure

(

@.conditionvarchar(20),

@.IDbigint,

@.date1as datetime,

@.date2as datetime

)

AS

/* SET NOCOUNT ON */

IF@.conditionLIKE'all'

SELECT CllientEventDetails.*

FROM CllientEventDetails

WHERE (ClientID = @.ID)

IF@.conditionLIKE'current_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventFrom <=ISNULL(@.date1, EventFrom))AND

(EventTill >=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'past_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventTill <=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'upcoming_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE(ClientID = @.ID)AND

(EventFrom >=ISNULL(@.date1, EventFrom))

IF@.conditionLIKE''

SELECT CllientEventDetails.*

FROM CllientEventDetails

RETURN

Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants

Hi,

Please check whether the @.condition parameter you have provided can hit in the IF statements. At the end, you don't need to use RETURN if you don't return anything.

I would not suggest you put the condition in your WHERE clause, because it will return an empty result set for the condition that does not meet. And multiple result sets will be returned for all the SELECT statements.

|||

Nitin Pawar:

Created a stored procedure which returns Selected table from database.

I pass variables,according to conditions

For some reason it is not returning any result for any condition

Stored Procedure

ALTER PROCEDUREdbo.StoredProcedure

(

@.conditionvarchar(20),

@.IDbigint,

@.date1as datetime,

@.date2as datetime

)

AS

/* SET NOCOUNT ON */

IF@.conditionLIKE'all'

SELECT CllientEventDetails.*

FROM CllientEventDetails

WHERE (ClientID = @.ID)

IF@.conditionLIKE'current_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventFrom <=ISNULL(@.date1, EventFrom))AND

(EventTill >=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'past_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE (ClientID = @.ID)AND

(EventTill <=ISNULL(@.date1, EventTill))

IF@.conditionLIKE'upcoming_events'

SELECT ClientEventDetails.*

FROM ClientEventDetails

WHERE(ClientID = @.ID)AND

(EventFrom >=ISNULL(@.date1, EventFrom))

IF@.conditionLIKE''

SELECT CllientEventDetails.*

FROM CllientEventDetails

RETURN

Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants

replaceLike by= and then try .. hope it will help

Concurrent transaction in READ COMMITED isolation level

Hello,
I currently work on Sql Serveur transactions (not distributed) and their
behaviors according to the selected isolation level.
I try to reproduce the default working of Oracle, and I have the following
problem:
With an isolation level READ COMMITED, by default Oracle "lock" no
request for reading modified records in another transaction (or connection)
not yet validated. Set apart the modifications made in the transaction in
progress, the result of a SELECT always return the "version" of the
validated recordings. Thus, there is no blocking in reading.
Sql Server 2000 function differently with the same isolation level. A
request SELECT is systematically blocked if the set of result must contain a
record new or modified not yet validated. Key word READPAST in request
SELECT solves only the problem related to the new records, the modified
record always block the request for reading!
Is there a solution?
Thank you in advance for your assistance.The key in either situation is to hold locks as short of a time span as
possible. READ COMMITTED puts a lock on an object, does it's thing with the
object, drops the lock on the object, and then moves to the next object
doing the same thing to the next object (usually we are talking about a row
in a table.)
So, in a well built system, the only contention is when you have a lot of
writes to rows that people also are trying to look at. So first question,
do you have people who need to see the data that people are actually
actively modifying? If so, then you do have a quandry. If not (and usually
not) then you may have indexing problems where you are scanning an entire
table instead of just some rows.
This is the place to start, but the crux of it all is simple, separate
readers from writers as much as possible, hold locks for as short a time
period as possible. It will be the same in 2005 when we get versioning as
well. The longer the locks, the more likely we get inconsistency and or
locking.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Christopher" <Dev@.effect.fr> wrote in message
news:utLZFZkWFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I currently work on Sql Serveur transactions (not distributed) and their
> behaviors according to the selected isolation level.
> I try to reproduce the default working of Oracle, and I have the following
> problem:
> With an isolation level READ COMMITED, by default Oracle "lock" no
> request for reading modified records in another transaction (or
> connection) not yet validated. Set apart the modifications made in the
> transaction in progress, the result of a SELECT always return the
> "version" of the validated recordings. Thus, there is no blocking in
> reading.
> Sql Server 2000 function differently with the same isolation level. A
> request SELECT is systematically blocked if the set of result must contain
> a record new or modified not yet validated. Key word READPAST in request
> SELECT solves only the problem related to the new records, the modified
> record always block the request for reading!
> Is there a solution?
> Thank you in advance for your assistance.
>|||Thank you for this explanation.
Just a precision according to my context:
I don't know if some people need frequently to see the data that others
are actually modifying, but it's a possibility in any business application.
It can be a problem in some situations.
However, in all cases, I'm agree that it's better to hold locks for a
short time (short transaction).
Christopher.
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> a crit dans le message de
news: OIIrzzlWFHA.132@.TK2MSFTNGP14.phx.gbl...
> The key in either situation is to hold locks as short of a time span as
> possible. READ COMMITTED puts a lock on an object, does it's thing with
> the object, drops the lock on the object, and then moves to the next
> object doing the same thing to the next object (usually we are talking
> about a row in a table.)
> So, in a well built system, the only contention is when you have a lot of
> writes to rows that people also are trying to look at. So first question,
> do you have people who need to see the data that people are actually
> actively modifying? If so, then you do have a quandry. If not (and
> usually not) then you may have indexing problems where you are scanning an
> entire table instead of just some rows.
> This is the place to start, but the crux of it all is simple, separate
> readers from writers as much as possible, hold locks for as short a time
> period as possible. It will be the same in 2005 when we get versioning as
> well. The longer the locks, the more likely we get inconsistency and or
> locking.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Christopher" <Dev@.effect.fr> wrote in message
> news:utLZFZkWFHA.2420@.TK2MSFTNGP12.phx.gbl...
>|||> I don't know if some people need frequently to see the data that others
> are actually modifying, but it's a possibility in any business
> application.
It is a possibility, though I find in quite a few cases blocking comes from
poor planning for separation rather than a need for two people to work on
the same rows.
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Christopher" <Dev@.effect.fr> wrote in message
news:ORK5XV4WFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Thank you for this explanation.
> Just a precision according to my context:
> I don't know if some people need frequently to see the data that others
> are actually modifying, but it's a possibility in any business
> application.
> It can be a problem in some situations.
> However, in all cases, I'm agree that it's better to hold locks for a
> short time (short transaction).
> Christopher.
>
> "Louis Davidson" <dr_dontspamme_sql@.hotmail.com> a crit dans le message
> de news: OIIrzzlWFHA.132@.TK2MSFTNGP14.phx.gbl...
>