Using globals in the FROM clause

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

SQL Server Development (2000)

Using globals in the FROM clause

Author

Topic

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-16 : 13:31:09

Is there a way to make the table names resolve from globals?

This results in errors for me:

SELECT
COUNT(*) AS IN_TABLE1_NOT_IN_TABLE2
FROM
@TABLE1 A
WHERE
NOT EXISTS (SELECT NULL FROM @TABLE2 B WHERE A.VAR1 = B.VAR1)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-16 : 13:36:03

What globals are you referring to? Could you explain what you are trying to do by showing us some sample data?

Tara Kizer
aka tduggan

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-16 : 14:37:45

Sure!

I want to create several queries comparing two tables. Rather than hard coding the table names I'd like to have them SET at the top so that I can change the tables in just one place.

Example:

DECLARE @TABLE1 VARCHAR(128)
DECLARE @TABLE2 VARCHAR(128)
SET @TABLE1 = 'TABLENAME1'
SET @TABLE2 = 'TABLENAME2'

Then I'll use those variables to resolve the table names in subsequent queries (sorry for the vocab confusion, in some of the languages I use they are called global variables)

SELECT
COUNT(*) AS IN_TABLE1_NOT_IN_TABLE2
FROM
@TABLE1 A
WHERE
NOT EXISTS (SELECT NULL FROM @TABLE2 B WHERE A.VAR1 = B.VAR1)

Does this make sense?
Is it time for me to learn to write 'dynamic' SQL Server code?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-16 : 14:47:35

Yes, you have to use dynamic SQL. There is no way to dynamically change the table name without dynamic SQL. But be warned, this is highly not recommended for performance and security reasons. This type of approach is usually indicative of a design problem in your database schema.

Tara Kizer
aka tduggan

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-17 : 10:16:49

thanks, time to learn dynamic sql server code

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources