How to set table name in dynamic SQL query?

Question

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.

You can't parameterise the table name. You just do it manually in the SET @SQLQuery step. — MatBailie
– MatBailie, Commented Dec 19, 2013 at 10:13
@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
– HellBaby, Commented Dec 19, 2013 at 10:14
@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
– MatBailie, Commented Dec 19, 2013 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
– HellBaby, Commented Dec 19, 2013 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
– MatBailie, Commented Dec 19, 2013 at 10:30

Charlieface · Accepted Answer · 2022-08-27 22:42:11Z

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'

edited Aug 27, 2022 at 22:42

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

answered Mar 16, 2015 at 17:04

galaxis

9458 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HABO Over a year ago

Tip: The best practice when assembling object names into dynamic SQL statements is to use QuoteName() to avoid problems with odd names, e.g. New Table with a space or reserved words like From.

Dan · Accepted Answer · 2017-10-20 11:07:20Z

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.

edited Oct 20, 2017 at 11:07

answered Dec 19, 2013 at 10:12

Dan

10.8k27 silver badges51 bronze badges

3 Comments

Neo Over a year ago

thanks but anyways i really dont want this one. can't pass as table name to sp_executesql :(

Govind Rai Over a year ago

@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.

GoldBishop Over a year ago

Another consideration is to also apply QuoteName around the variable. That will properly wrap the table name with object wrappers, defaults to square brackets [...]

Saharsh Shah · Accepted Answer · 2013-12-19 10:12:46Z

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

answered Dec 19, 2013 at 10:12

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

3 Comments

Ji_in_coding Over a year ago

definitely the complete answer I was looking for.

Dan Bechard Over a year ago

Brilliant! Solved my problem perfectly.

Cody Konior Over a year ago

Open to SQL injection. All you need to do is pass a bad @TableName parameter such as 'sys.databaeses; Alter Server Role sysadmin Add Member [BadGuy]; --' and there goes your system.

CA Martin · Accepted Answer · 2019-08-19 03:18:56Z

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

answered Aug 19, 2019 at 3:18

CA Martin

3672 silver badges8 bronze badges

Comments

outofcoolnames · Accepted Answer · 2020-01-16 11:50:45Z

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

Collectives™ on Stack Overflow

How to set table name in dynamic SQL query?

5 Answers 5

1 Comment

3 Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

3 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related