Case in From 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)

Case in From clause

Author

Topic

rozialishah
Starting Member

3 Posts

Posted - 2008-02-08 : 10:53:50

Hi,
Can I use Case in From clause?
e.g
select *
from
case
when @input='yes' then local.table1
else remote.talble1

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-08 : 11:03:59

In short, yes. The result of a CASE statement is a value, not a conditional, so you need to use that value in a conditional somehow. mycol = [case statememt] for example. You can do things like mycol like '%' + [case statement] + '%' or any other conditional you can craft in a From. Here is an example for you.


Declare @input varchar(50)
Set @input = 'yes'

Select 
	SO.name As TableName,
	SC.name As ColumnName,
	SC.colorder As ColumnOrder
From sysobjects SO
Inner Join syscolumns SC On SO.id = SC.id
Where SO.xtype = 'U' And

--You can use a Case statement, but the
--result of a Case statement is a value,
--so you have to do something with that
--value.  Note how I use it here.
SO.Name = Case 
	When @input = 'yes' Then SO.name 
	Else SC.name
End

rozialishah
Starting Member

3 Posts

Posted - 2008-02-08 : 11:10:38

quote:
Originally posted by Qualis

In short, yes. The result of a CASE statement is a value, not a conditional, so you need to use that value in a conditional somehow. mycol = [case statememt] for example. You can do things like mycol like '%' + [case statement] + '%' or any other conditional you can craft in a From. Here is an example for you.

Declare @input varchar(50)
Set @input = 'yes'

Select
SO.name As TableName,
SC.name As ColumnName,
SC.colorder As ColumnOrder
From sysobjects SO
Inner Join syscolumns SC On SO.id = SC.id
Where SO.xtype = 'U' And

--You can use a Case statement, but the
--result of a Case statement is a value,
--so you have to do something with that
--value. Note how I use it here.
SO.Name = Case
When @input = 'yes' Then SO.name
Else SC.name
End

Thank you friend. I used case in where clause and order by clause but my question is can I use Case in From clause. in my case the data may be comming from local database or the live database but I can only write store procedure to my local database because live db is not our property.

Qualis
Posting Yak Master

145 Posts

Posted - 2008-02-08 : 11:48:31

Oops! Sorry, I missed that. No, you can't use a Case statement in your From clause. So, your stored proc cannot be in Live database at all but must query from it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-08 : 12:20:52

Will the table name differ in live & local db? if you really want to coinditionally select object then you need to use dynamic sql.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-02-08 : 13:59:22

you should REALLY determine which database you are hitting BEFORE you make the call. Really. If you KNOW you are going to hit the Production db, then make your connection string hit it, and not your test.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

rozialishah
Starting Member

3 Posts

Posted - 2008-02-08 : 14:26:30

So it will be better way to use direct query approach rather than stored procedure. Can I use TSQL in plane query and send it to database to execute it direclty without calling store procedure?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-08 : 14:37:49

No, I don't think that was the point. You should stick with using SPs. The application should have a config file that defines environment specific variables (like connection strings). That way, your sql code and application code is unchanged when you move across environments (from Dev to QC to Acceptance to Production). All you just need to modify is the config file. That type of intellegence should not be "built into" the code.

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-09 : 01:31:47

and incase if you want to get data from tables reside in the same database based on input value

If @input='yes'
select * from table1
else
select * from table2

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources