Parameter in Where Clause - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Parameter in Where Clause

Author

Topic

kellog1
Starting Member

35 Posts

Posted - 2010-07-19 : 20:13:17

I would like to get result set based on parameter value.

Here is my query

SELECT *
FROM Table1 t1
JOIN Table2 t2
ON t1.Id = t2.Id
WHERE CASE @StateLocalIndicator
WHEN 'S/L' THEN t1.StateLocal
WHEN 'PCL' THEN t2.PCLStateLocal
END

This query is giving me error.

Any help is appreciated.

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2010-07-19 : 21:46:10

Is the variable @StateLocalIndicator your column name? For which table?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-20 : 03:43:08

Did you try this?

SELECT *
FROM Table1 t1
JOIN Table2 t2
ON t1.Id = t2.Id
WHERE @StateLocalIndicator=CASE @StateLocalIndicator
WHEN 'S/L' THEN t1.StateLocal
WHEN 'PCL' THEN t2.PCLStateLocal
END

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-07-20 : 03:52:13

Needs some comparison-to column I think Madhi?


WHERE    (@StateLocalIndicator = 'S/L' AND SomeColumn = t1.StateLocal)
      OR (@StateLocalIndicator = 'PCL' AND SomeColumn = t2.PCLStateLocal)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-20 : 05:14:38

quote:
Originally posted by Kristen

Needs some comparison-to column I think Madhi?

WHERE (@StateLocalIndicator = 'S/L' AND SomeColumn = t1.StateLocal)
OR (@StateLocalIndicator = 'PCL' AND SomeColumn = t2.PCLStateLocal)

It may be. OP needs to confirm it

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-07-20 : 06:50:29

Will your work Madhi?

I haven't tried it - but i.e. does a simple

WHERE True_or_False_expression

logic statement work in SQL? (just curious to know!)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-20 : 10:05:35

I think the code

SELECT *
FROM Table1 t1
JOIN Table2 t2
ON t1.Id = t2.Id
WHERE @StateLocalIndicator=CASE @StateLocalIndicator
WHEN 'S/L' THEN t1.StateLocal
WHEN 'PCL' THEN t2.PCLStateLocal
END

is equivalent to

SELECT *
FROM Table1 t1
JOIN Table2 t2
ON t1.Id = t2.Id
WHERE
(@StateLocalIndicator='S/L' and t1.StateLocal=@StateLocalIndicator)
OR
(@StateLocalIndicator='PCL' and t2.PCLStateLocal =@StateLocalIndicator)

Isn't it?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-07-20 : 12:23:16

You are right, I've read it more carefully now :)

kellog1
Starting Member

35 Posts

Posted - 2010-07-20 : 21:50:03

Thanks guys.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-21 : 05:05:24