Using variable in a select 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 2005 Forums

SQL Server Administration (2005)

Using variable in a select statement

Author

Topic

sanjula_n
Starting Member

6 Posts

Posted - 2012-02-07 : 00:42:23

DECLARE @db_name VARCHAR(50)
DECLARE @date DATE

SET @db_name = 'db_one'
SET @date = '01-jan-2012'

SELECT * FROM @db_name.[table_name] WHERE [date] = @date

This statement throws error. Can someone tell me how to rectify this error.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-07 : 00:51:51

You have to use dynamic SQL for that. But why do you need to set the database name dynamically? Just how many do you have that are all the same?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-07 : 00:52:26

Read this for the curse and blessings of dynamic SQL: http://www.sommarskog.se/dynamic_sql.html

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sanjula_n
Starting Member

6 Posts

Posted - 2012-02-07 : 01:08:57

Thanks for the link.

I have 3 databases 2(db_one & db_three) having same structure but different data.Whatever statements I've posted are part of a stored procedure in database 'db_two', using this procedure I need to access the tables in 'db_one' or 'db_three' based on constraints (tables have same structure and name).

So based on a condition I might access from db_one or db_three.

sanjula_n
Starting Member

6 Posts

Posted - 2012-02-07 : 01:15:39

Is there any other way other than dynamic sql???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-07 : 12:35:39

You have to use dynamic SQL for this. The only other way is to redesign your system so that it all is in one database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-07 : 13:26:26

quote:
Originally posted by sanjula_n

Is there any other way other than dynamic sql???

CREATE PROC mySproc99
@dbName varchar(255)
AS
BEGIN

IF @DBName NOT IN ('xyz','abc')
GOTO sp_Exit

IF @DBName = 'xyz'
BEGIN
SELECT * FROM xyz.dbo.Table
END

IF @DBName = 'abc'
BEGIN
SELECT * FROM abc.dbo.Table
END

ect

END

sp_Exit:

RETURN

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2012-02-07 : 14:00:30

quote:
Originally posted by sanjula_n

Is there any other way other than dynamic sql???


SELECT * 
FROM [db_one]..[table_name] 
WHERE [date] = @date
      AND @db_name = 'db_one'
UNION ALL

SELECT * 
FROM [db_two]..[table_name] 
WHERE [date] = @date
      AND @db_name = 'db_two'

that's fine for a finite number of specifically named databases, but it sure as heck does not scale!

sanjula_n
Starting Member

6 Posts

Posted - 2012-02-10 : 04:00:39

My scenario is like this

create proc [db_2].[dbo].[proc2]
(
@db_name VARCHAR(50)
,@date DATE
)
As
BEGIN

SELECT *
INTO @db_name.[dbo].[table2]
FROM [db_2].[dbo].[table1]
WHERE [date] = @date

END

create proc [db_2].[dbo].[proc1]
(
@flag INT
)
AS
BEGIN
IF (@flag = 1 )
BEGIN
EXEC [db_2].[dbo].[proc2] db_1,'01-jan-2011'
END
ELSE
BEGIN
EXEC [db_2].[dbo].[proc2] db_3,'01-feb-2011'
END
END

EXEC [db_2].[dbo].[proc1] 1

Can someone help me ???

Kristen
Test

22859 Posts

Posted - 2012-02-10 : 04:54:46

What problem did you have using my method, or dynamic SQL as others suggested?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-10 : 05:03:03

Could you create a view (in any db) over the others? Something like (but using names rather than *)


CREATE VIEW table1_All_Dbs AS
		  SELECT 'DB1' AS [DB], * FROM db1.dbo.table1
UNION ALL SELECT 'DB2' AS [DB], * FROM db2.dbo.table1
UNION ALL SELECT 'DB3' AS [DB], * FROM db3.dbo.table1

You can use synonyms to reference tables in another db as if they were inside your first database also.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-02-10 : 05:04:33

Also -- why is your setup like this? does each database represent a different client or other discrete entity?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2012-02-10 : 07:38:21

quote:
Originally posted by Transact Charlie

Could you create a view (in any db) over the others?

Nice idea TC as only a single place to add any new database that is created.

But I still think its not workable if there are lots of databases, or they aren't "finite" as it doesn't really scale very well - and that (if that's the case) dynamic SQL would be better ... or, even better, a redesign to cater for the issue properly - Band-Aid is rarely the right solution!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources