HELP! Dynamic binding of schema element names

Hi all,
I'm trying to achieve the following using SQL and SQLServer2000 is the db
I'm using.
Here's a simple select statement
SELECT column1, column2 FROM my_table WHERE some_condition = 1
What I want to be able to do is bind the name my_table to an actual
tablename during runtime, i.e. when the query executes.
The equivalent effect of what I'd like can be represented as below:
SELECT column1, column2 FROM get_my_table_name( ) WHERE some_condition = 1.
Here get_my_table_name( ) is a function that evaluates and returns the table
name. Things don't work this way however.
Is there a way to accomplish this?
Any replies are greatly appreciated.
Thanks in advance,
--Abhi[posted and mailed, please reply in news]
Abhijith Das (adas@.expeditevcs.com) writes:
> I'm trying to achieve the following using SQL and SQLServer2000 is the db
> I'm using.
> Here's a simple select statement
> SELECT column1, column2 FROM my_table WHERE some_condition = 1
> What I want to be able to do is bind the name my_table to an actual
> tablename during runtime, i.e. when the query executes.
> The equivalent effect of what I'd like can be represented as below:
> SELECT column1, column2 FROM get_my_table_name( ) WHERE some_condition => 1.
> Here get_my_table_name( ) is a function that evaluates and returns the
> table name. Things don't work this way however. Is there a way to
> accomplish this?
Yes. But it is unlikely that it is the right thing to do. Since I don't
know your underlying problem, I cannot suggest a solution here and now.
But this article on my web site, both describes on how you can achieve
this - and why you most probably should not do it anyway.
http://www.sommarskog.se/dynamic_sql.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Hi ,
No you can't do that that way. You must use dynamic SQL something like this
Declare @.SQL varchar(1000)
set @.SQL = 'SELECT column1, column2 FROM ' + 'my_table' + ' WHERE
some_condition = 1'
exec @.SQL
--
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Abhijith Das" <adas@.expeditevcs.com> wrote in message
news:u400jnbqFHA.544@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> I'm trying to achieve the following using SQL and SQLServer2000 is the db
> I'm using.
> Here's a simple select statement
> SELECT column1, column2 FROM my_table WHERE some_condition = 1
> What I want to be able to do is bind the name my_table to an actual
> tablename during runtime, i.e. when the query executes.
> The equivalent effect of what I'd like can be represented as below:
> SELECT column1, column2 FROM get_my_table_name( ) WHERE some_condition => 1.
> Here get_my_table_name( ) is a function that evaluates and returns the
> table name. Things don't work this way however.
> Is there a way to accomplish this?
> Any replies are greatly appreciated.
> Thanks in advance,
> --Abhi
>