Application Delivery Management
Application Modernization & Connectivity
CyberRes
IT Operations Management
Is there a way to access a Microsoft SQL Server table with AcuSQL using a variable to set the table name?
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.