Problem:

Is there a way to access a Microsoft SQL Server table with AcuSQL using a variable to set the table name?

Resolution:

This can be done using dynamic SQL.  The following link to an article by Nigel Rivett has an example of how to do this:

http://www.nigelrivett.net/SQLTsql/TableNameAsVariable.html

Excerpts from the article provide a detailed explanation:

This is a simple problem to solve but has a number of consequences.

It is often because you wish to pass a table name to a stored procedure

perhaps processing all tables in a database.

Of course

select * from @TableName

will not work as the variable will not be resolved as containing the name of a table.

The method used here is dynamic SQL - executing a built string.

Create procedure s_ProcTable

@TableName varchar(128)

as

declare @sql varchar(4000)

            select @sql = 'select rows = count(*) from [' @TableName ']'

            exec (@sql)

go

Now executing this will give the desired result.

Note the [ ] around the name in case it contains invalid characters.

You may also have to deal with the owner.

now

exec s_ProcTable 'sysobjects'

exec s_ProcTable 'syscolumns'

gives the result for these tables

Warning

Consider

declare @s varchar(128)

select @s = 'sysobjects]' char(13) char(10) 'go'   char(13) char(10) 'select * from [syscolumns'

or even just

select @s = 'sysobjects] select * from [syscolumns'

exec s_ProcTable @s

This is called SQL injection and the given sp will allow the user to execute any query they wish on the server (even drop a database if the permissions are correct).

Do not try to use this with input from a non-trusted user without being very careful.

It also means that the user will need to be given permission on the objects accessed as this will not be inherited from the SP.