Variable database in From statement

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

Transact-SQL (2000)

Variable database in From statement

Author

Topic

schwarzt

3 Posts

Posted - 2006-05-26 : 11:33:35

Can anyone help me? I need to select a database with a variable? I want to say …

SELECT *
FROM (@FROM).dbo.INSUL_SQL

Where @From could be (DATA, DATA_01, DATA_02 ect…) and @From is passed from SQL Reporting services.

Or use some sort of case or if/then logic like

CASE
WHEN @From = ‘DATA’THEN SELECT * FROM DATA.dbo.INSUL_SQL
WHEN @From = ‘DATA_01’THEN SELECT * FROM DATA_01.dbo.INSUL_SQL
WHEN @From = 'DATA_02'THEN SELECT * FROM DATA_02.dbo.INSUL_SQL
END

Newbie to SQL so be gentle.

T

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-26 : 11:43:35

>>SELECT *
>>FROM (@FROM).dbo.INSUL_SQL

You cant use that directly without using dynamic sql
Instead of using case use if


If @From = 'DATA'
        SELECT * FROM DATA.dbo.INSUL_SQL
else if @From = 'DATA_01' 
	SELECT * FROM DATA_01.dbo.INSUL_SQL
else if @From = 'DATA_02' 
	SELECT * FROM DATA_02.dbo.INSUL_SQL

Madhivanan

Failing to plan is Planning to fail

schwarzt

3 Posts

Posted - 2006-05-26 : 11:57:15

PERFECT!! Thanks so much, It worked like a charm. What is Dynamic SQL?

Travis

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-26 : 12:10:21

schwarzt,

Use of "CASE", in TSQL is quite different from any other language (like VB)

Dynamic SQL is a Query built with passed parameters, variables and String Constants
That SQL string is executed by an exec command.

Srinika

schwarzt

3 Posts

Posted - 2006-05-26 : 12:24:28

Got it, thanks. Obviously I've got some reading to do.

T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources