Creating SQL with flexible table names

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)

Creating SQL with flexible table names

Author

Topic

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2008-07-29 : 15:23:56

Let's say I have the code


USE DB1A
GO
CREATE FUNCTION Function1 [...]
GO
CREATE SPROC sp_Sproc1 (@1ColumnName, @2ColumnName) AS
EXEC(
'SELECT * FROM DB1A.Table1 INNER JOIN DB2A.Table1 on DB1A.Table1.' + 
@1ColumnName + ' =Function1(DB2A.Table1.' + @2ColumnName + ')'
)
GO
EXEC DB1A.sp_Sproc1 (Column1, Column1)
GO
EXEC DB1A.sp_Sproc1 (Column2, Column2)
GO

Now, if someone else had the same databases as me, except they were named DB1 and DB12, they could run a find-and-replace to Fix up my code for their use. But what if they wanted a variable that could change what the database name was at the top?

Making DB1A a variable is easy:


DECLARE @FirstDB varchar(200);
SET @FirstDB = 'DB1A';
EXEC ('USE ' + @DB);
GO

Then, just delete all future references to DB1A. But how would I set @SecondDB and pass it over a GO statement to run in a batch?

I can't even make it all a big sproc with terms sp_MaserSproc(@FirstDB, @SecondDB) because of all of the GO statements...right? Or can I?

Thanks,
Arithmomaniac

---------
Ignorance may be bliss, but knowledge is thrill.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-29 : 15:43:38

It looks like you are pretty far down a "solution-path" that is just not a good design. I think answering your specific question (which by the way there wasn't any) would not be of service to you. I think you should step back and explain your big-picture objectives. There are better ways...

Be One with the Optimizer
TG

Arithmomaniac
Yak Posting Veteran

66 Posts

Posted - 2008-07-30 : 14:13:41

I created the variables at the top of the page and dumped them into a temporary variable. When I needed them, I called the table:

DECLARE @FirstDB varchar(50);
DECLARE @SecondDB varchar(50);
SET @FirstDB = 'DB1A';
SET @SeconDB = 'DB1B';
IF EXISTS(
  SELECT * FROM Tempdb.dbo.Sysobjects
    WHERE Name = '##NameHolder')
DROP TABLE ##NameHolder;
CREATE TABLE ##NameHolder(
  DBType tinyint,
  DBName varchar(50)
);
INSERT INTO ##NameHolder VALUES
(1, @FirstDB);
INSERT INTO ##NameHolder VALUES
(2, @SecondDB);
GO
DECLARE @FirstDB varchar(50);
SELECT @FirstDB = (SELECT DBName FROM ##NameHolder WHERE DBType = 1);
EXEC SPROC (@FirstDB)

---------
Ignorance may be bliss, but knowledge is thrill.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources