How to add extra condition during where condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to add extra condition during where condition

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2011-02-14 : 02:15:07

Dear All,

Select * FROM [dbo].[tbshipping]
where fromcountryid = 130 and tocountryid=130
if(canState=='Y') and fromstateid=120 and tostateid = 120

Above is my sql statement which not working. How can i check if tbShipping canState Field return value Y then oni check fromstate & to stateValue. If canState value='N' then it'll ignore this line >> and fromstateid=120 and tostateid = 120.

Please Advise.

Thank you.

Regards,
Micheale

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-02-14 : 03:04:18

Your best bet in this situation is dynamic sql:

declare @sql nvarchar(2000)

set @sql = 'Select * FROM [dbo].[tbshipping] where fromcountryid = 130 and tocountryid=130'
if(@canState='Y') 
  set @sql = @sql + 'and fromstateid=120 and tostateid = 120'

exec sp_executesql @sql

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

micnie_2020
Posting Yak Master

232 Posts

Posted - 2011-02-14 : 03:12:17

Dear Lumbago,

canState is not from outside passing data. Is inside the tbShipping.

Tqvm.

Regards,
Micheale

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-02-14 : 03:40:59

Ok, sort of funky requirement but try this:

Select * 
FROM [dbo].[tbshipping] 
where fromcountryid = 130 
  and tocountryid=130 
  and fromstateid = case when canState = 'Y' then 120 else fromstateid end
  and tostateid = case when canState = 'Y' then 120 else tostateid end

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-02-14 : 03:53:39

It's Monday morning and my brain isn't going full pace yet, but I think this would be a more standard way of doing it:

Select * 
FROM [dbo].[tbshipping] 
where fromcountryid = 130 
  and tocountryid=130 
  and canState = 'Y'
  and fromstateid = 120
  and tostateid = 120
union all 
Select * 
FROM [dbo].[tbshipping] 
where fromcountryid = 130 
  and tocountryid=130 
  and canState <> 'Y'

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-14 : 04:29:37

[code]-- Example 1
SELECT *
FROM dbo.tbShipping
WHERE FromCountryID = CASE WHEN CanState = 'Y' THEN 120 ELSE 130 END
AND ToCountryID = CASE WHEN CanState = 'Y' THEN 120 ELSE 130 END

-- Example 2
SELECT *
FROM dbo.tbShipping
WHERE FromCountryID = 130
AND ToCountryID = 130
AND CanState <> 'Y'

UNION ALL

SELECT *
FROM dbo.tbShipping
WHERE FromCountryID = 120
AND ToCountryID = 120
AND CanState = 'Y'[/code]

N 56°04'39.26"
E 12°55'05.63"

micnie_2020
Posting Yak Master

232 Posts

Posted - 2011-02-14 : 04:47:18

Thanx for replying.

Not the answer i want. Sorry, mayb my explanation on top is not clear.

Select * FROM [dbo].[tbshipping] where fromcountryid = 130 and tocountryid=130

First it'll have above sql & trying to return canState. If canState return Y mean further checking like below
Select * FROM [dbo].[tbshipping] where fromcountryid = 130 and tocountryid=130
and fromstateid=@fState and tostateid=@tstate

if canState return N. mean stop checking at there. Mean no more checking on fromstateid & tostateid.

Just wondering, can ms sql handle this complicated issue or not.

Thank you.

Regards,
Micheale

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-02-14 : 05:05:35

Did you try my second suggestion with the union all? As far as I can understand it will return the results you have specified...

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

javad_ghasemiro
Starting Member

5 Posts

Posted - 2011-02-14 : 12:06:50

this is simplest way

Select * FROM [dbo].[tbshipping]
where fromcountryid = 130
and tocountryid=130
and (
( canState='Y' and fromstateid=120 and tostateid = 120)
or
( canState<> 'Y' )
)

Javad Ghasemi

micnie_2020
Posting Yak Master

232 Posts

Posted - 2011-02-16 : 00:12:51

Dear Javad,

Thank you so much.
Never taught of your method. Is really simplest way & workable.

Regards,
Micheale

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-16 : 01:33:52

Yes. OR in the query produces lovely execution plans.

N 56°04'39.26"
E 12°55'05.63"

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-16 : 01:42:09