Dynamically change the Table Name used in 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 2000 Forums

SQL Server Development (2000)

Dynamically change the Table Name used in SELECT statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-04-11 : 08:16:21

Bibek writes "Hello Team,

I want to dynamically change the table name in the SELECT clause.Below is what I intend to do.

Declare
@vTransactionId int,
@vObjectName varchar(30)

Select @vTransactionId=24200,@vObjectName='AccountContacts' -- Please set the TransactionID & ObjectName

SELECT a.accountid,a.contactid,a.operation, b.errorshortdesc,b.errorlongdesc,c.additionaldetails FROM
@vObjectName a(nolock),Error_Type b(nolock), error_log c(nolock)
WHERE a.transactionid=c.transactionid AND b.errortypeid=c.errortypeid AND a.transactionid = @vTransactionId

I am getting error "Must declare the variable '@vObjectName'" in the SELECt statement.

Pl help me to find a solution.

Thanks,
Bibek"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-04-11 : 08:31:50

"dynamically change the table name in the SELECT "...solved by DYNAMIC SQL...search here for examples and advice on limitations (problems) on going down this route.

kitwest
Starting Member

2 Posts

Posted - 2005-04-11 : 17:36:21

As an alternative to creating dynamic SQL, you might consider creating a view with a UNION ALL select of the tables, adding a TableName column to denote the base table name:

SELECT a.accountid, a.contactid, a.operation,
b.errorshortdesc, b.errorlongdesc, c.additionaldetails
FROM vwUnion, Error_Type b(nolock), error_log c(nolock)
WHERE a.transactionid = c.transactionid
AND b.errortypeid = c.errortypeid
AND a.transactionid = @vTransactionId
AND TableName = @vObjectName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources