dynamic table name to be passed in sql query

Question

I want to execute a sql query by using dynamic table name.

I tried below codes but none of these works for me and throws syntax error.

exec sp_executesql N'SELECT * FROM  @Table1 D WITH (NOLOCK)',
    N'@Table1 nvarchar(40)',@Table1=N'master.dbo.ABCD_data'

OR

exec sp_executesql N'SELECT * FROM  master.dbo.+@Variable1+_data D WITH (NOLOCK)',
    N'@Variable1 nvarchar(4),',@Variable1=N'ABCD'

Can anybody help me in creating this dynamic table name ?

I get this query from sql profiler. Actually i m executing it in c# code and got this in profiler.

My C# code is very simple, its kind of this:

string query = @"SELECT * FROM master.dbo.+@Variable1+_data D WITH (NOLOCK)";
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Variable1", "ABCD");
DataTable dt = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
    adapter.Fill(dt);
}

This question is already answered here: http://stackoverflow.com/a/1246848/109122 — RBarryYoung, Jul 16 '15 at 13:30
@GauravGogna No it isn't. What's stated there will work here as well, and will also solve the SQL Injection vulnerability you're about to introduce. — RBarryYoung, Jul 16 '15 at 20:00
@RBarryYoung: This isn't the best of questions, but the supposed duplicate doesn't even mention C#. I don't think you should mark questions as duplicate because of their poor quality. They have to be exact duplicates. — Andomar, Jul 17 '15 at 06:19
@Andomar, No they don't have to be *exact* to be closed as duplicates, but I agree that should be pretty similar. However, I wasn't claiming it was a duplicate either. I was stating that this question is in fact answered by the indicated link. As for poor quality, I'll probably just downvote it. — RBarryYoung, Jul 17 '15 at 11:41

Andomar · Answer 1 · 2015-07-16T13:35:10.593

-1

You can't pass table or column names as parameters.

The usual way around that is called "dynamic SQL":

declare @sql nvarchar(max);
set @sql = 'select * from ' + quotename(@tablename);
exec (@sql);

Or to pass both table names and parameter values:

declare @sql nvarchar(max);
set @sql = 'select * from ' + quotename(@tablename) + ' where col1 = @par1';
exec sp_executesql @sql, N'@par1 nvarchar(4)', @par1='val1';

A naive way to run this from C# (vulnerable to SQL injection):

SqlCommand cmd = new SqlCommand();
cmd.CommandText = @"
    declare @sql nvarchar(max);
    set @sql = 'select * from " + "ABCD" + @"_data d with (nolock)';
    exec (@sql);
    ";
cmd.CommandType = CommandType.Text;
DataTable dt = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
    adapter.Fill(dt);

To run this from C# while still using the quotename function:

SqlCommand cmd = new SqlCommand();
cmd.CommandText = @"
    declare @sql nvarchar(max);
    set @sql = 'select * from ' + quotename(@tablename) + '_data d with (nolock)';
    exec (@sql);
    ";
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@tablename", "ABCD");
DataTable dt = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
    adapter.Fill(dt);

edited Jul 16 '15 at 13:35

answered Jul 16 '15 at 12:55

Andomar

232,371
49
380
404

1

Just be very careful you dfon't introduce SQL Injection vulnerabilities by being careful how `@tablename` gets its value. – Richard Jul 16 '15 at 12:56
3

You have a gold badge in [sql-server], did you believe this question had never been asked before? – Tanner Jul 16 '15 at 13:05
@Tanner: Each variant is slightly different. This question includes `sp_executesql` with parameters, which the supposed "exact duplicate" does not. The supposed duplicate top answer refers to [Sommarskog](http://www.sommarskog.se/dynamic_sql.html) which is a resource for the most advanced of SQL Server users, a clear mismatch for this type of question. – Andomar Jul 16 '15 at 13:12
hy guys thanks for trying but my case is different. i can't create the @sql variable and then execute it using exec(@sql) as Andomar did. Actually I ran this query in c# code and got this query generated dynamically from sql profiler. – Gaurav Gogna Jul 16 '15 at 13:15
@Andomar No luck bro. I tried your way but not working. – Gaurav Gogna Jul 16 '15 at 14:45

dynamic table name to be passed in sql query

1 Answers1