33

I want to set table name in a dynamic SQL query. I tried successfully for parameter as following:

/* Using sp_executesql */
/* Build and Execute a Transact-SQL String with a single parameter 
value Using sp_executesql Command */

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
/* set the parameter value */
SET @EmpID = 1001
/* Build Transact-SQL String by including the parameter */
SET @SQLQuery = 'SELECT * FROM tblEmployees WHERE EmployeeID = @EmpID' 
/* Specify Parameter Format */
SET @ParameterDefinition =  '@EmpID SMALLINT'
/* Execute Transact-SQL String */
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID

Now I want to take TABLE NAME dynamically using a parameter but I've failed to do that. Please guide me.

TT.
  • 15,774
  • 6
  • 47
  • 88
Neo
  • 15,491
  • 59
  • 215
  • 405
  • 1
    You can't parameterise the table name. You just do it *manually* in the `SET @SQLQuery` step. – MatBailie Dec 19 '13 at 10:13
  • 1
    @MatBailie actually you can pas the table name using it as a string and using exec of the string name(it behave exactly like a procedure) – HellBaby Dec 19 '13 at 10:14
  • 1
    @hellbaby - And still you can't supply the table name as a *parameter* to `sp_executesql`, this can only be accomplished with a substitution into the string. These are VERY different. Parameterisation allows type checking, protection from SQL Injection Attacks, execution plan reuse, etc. Substituting strings into other strings does none of that and so is *not* parameterisation. – MatBailie Dec 19 '13 at 10:21
  • @MatBailie actually you don't need the 'sp_executesql' command, in mssql 2008+ it's enough just to call in this way: exec @ myqueryconcatenatedvariablewithtablename and you got your answer; regarding injections - maybe you're right(someone with skills can break it). So depends where he need to use this after all... – HellBaby Dec 19 '13 at 10:26
  • @HellBaby - That's exactly the same mechanism - You're just ***substituting*** the table name into your string and then executing the resulting string. At no point in your example is the table name a ***parameter***. – MatBailie Dec 19 '13 at 10:30

5 Answers5

53

To help guard against SQL injection, I normally try to use functions wherever possible. In this case, you could do:

...
SET @TableName = '<[db].><[schema].>tblEmployees'
SET @TableID   = OBJECT_ID(TableName) --won't resolve if malformed/injected.
...
SET @SQLQuery = 'SELECT * FROM ' + QUOTENAME(OBJECT_NAME(@TableID)) + ' WHERE EmployeeID = @EmpID'
Charlieface
  • 52,284
  • 6
  • 19
  • 43
galaxis
  • 925
  • 8
  • 10
  • Tip: The best practice when assembling object names into dynamic SQL statements is to use [`QuoteName()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/quotename-transact-sql) to avoid problems with odd names, e.g. `New Table` with a space or reserved words like `From`. – HABO Dec 10 '21 at 20:49
41

Table names cannot be supplied as parameters, so you'll have to construct the SQL string manually like this:

SET @SQLQuery = 'SELECT * FROM ' + @TableName + ' WHERE EmployeeID = @EmpID'

However, make sure that your application does not allow a user to directly enter the value of @TableName, as this would make your query susceptible to SQL injection. For one possible solution to this, see this answer.

Dan
  • 10,480
  • 23
  • 49
  • thanks but anyways i really dont want this one. can't pass as table name to sp_executesql :( – Neo Dec 19 '13 at 10:26
  • @Neo You should be able to use `sp_executesql` since you should already have the the correct table name inserted when setting your SQL string. This is a primary reason why SQL strings are constructed manually in the first place. – Govind Rai Feb 09 '16 at 15:35
  • 2
    Another consideration is to also apply `QuoteName` around the variable. That will properly wrap the table name with object wrappers, defaults to square brackets `[...]` – GoldBishop May 04 '17 at 14:51
7

Try this: 

/* Variable Declaration */
DECLARE @EmpID AS SMALLINT
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
DECLARE @TableName AS NVARCHAR(100)
/* set the parameter value */
SET @EmpID = 1001
SET @TableName = 'tblEmployees'
/* Build Transact-SQL String by including the parameter */
SET @SQLQuery = 'SELECT * FROM ' + @TableName + ' WHERE EmployeeID = @EmpID' 
/* Specify Parameter Format */
SET @ParameterDefinition =  '@EmpID SMALLINT'
/* Execute Transact-SQL String */
EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @EmpID
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
2

This is the best way to get a schema dynamically and add it to the different tables within a database in order to get other information dynamically

select @sql = 'insert #tables SELECT ''[''+SCHEMA_NAME(schema_id)+''.''+name+'']'' AS SchemaTable FROM sys.tables'

exec (@sql)

of course #tables is a dynamic table in the stored procedure

CA Martin
  • 307
  • 2
  • 7
2

Building on a previous answer by @user1172173 that addressed SQL Injection vulnerabilities, see below:

CREATE PROCEDURE [dbo].[spQ_SomeColumnByCustomerId](
@CustomerId int,
@SchemaName varchar(20),
@TableName nvarchar(200)) AS
SET Nocount ON
DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(100)
DECLARE @Table_ObjectId int;
DECLARE @Schema_ObjectId int;
DECLARE @Schema_Table_SecuredFromSqlInjection NVARCHAR(125)

SET @Table_ObjectId = OBJECT_ID(@TableName)
SET @Schema_ObjectId = SCHEMA_ID(@SchemaName)
SET @Schema_Table_SecuredFromSqlInjection = SCHEMA_NAME(@Schema_ObjectId) + '.' + OBJECT_NAME(@Table_ObjectId)

SET @SQLQuery = N'SELECT TOP 1 ' + @Schema_Table_SecuredFromSqlInjection + '.SomeColumn 
FROM dbo.Customer 
INNER JOIN ' + @Schema_Table_SecuredFromSqlInjection + ' 
ON dbo.Customer.Customerid = ' + @Schema_Table_SecuredFromSqlInjection + '.CustomerId 
WHERE dbo.Customer.CustomerID = @CustomerIdParam 
ORDER BY ' + @Schema_Table_SecuredFromSqlInjection + '.SomeColumn DESC' 
SET @ParameterDefinition =  N'@CustomerIdParam INT'

EXECUTE sp_executesql @SQLQuery, @ParameterDefinition, @CustomerIdParam = @CustomerId; RETURN
outofcoolnames
  • 182
  • 2
  • 5