Parameterise table name in .NET/SQL?

You cannot directly parameterize the table name. You can do it indirectly via sp_ExecuteSQL, but you might just as well build the (parameterized) TSQL in C# (concatenating the table-name but not the other values) and send it down as a command. You get the same security model (i.e. you need explicit SELECT etc, and assuming it isn't signed etc).

Also - be sure to white-list the table name.

SQL query parameters can only take the place of a literal value. You cannot use a parameter for a table name, column name, list of values, or other SQL syntax. That's standard SQL behavior across all brands of database.

The only way to make the table name dynamic is to interpolate a variable into your SQL query before you prepare that string as a statement.

BTW, you're fooling yourself if you think this isn't a risk for SQL injection. If you interpolate the table name into the query dynamically, you need to use delimited identifiers around the table name, just as you would use quotes around a string literal that is interpolated from a variable.

I don't think I've ever seen this capability in any SQL dialect I've seen, but it's not an area of expertise.

I would suggest restricting the characters to A-Z, a-z, 0-9, '.', '_' and ' ' - and then use whatever the appropriate bracketing is for the database (e.g. [] for SQL Server, I believe) to wrap round the whole thing. Then just place it directly in the SQL.

It's not entirely clear what you meant about it not being a SQL injection risk - do you mean the names will be in source code and only in source code? If so, I agree that makes things better. You may not even need to do the bracketing automatically, if you trust your developers not to be cretins (deliberately or not).

You can pass the table name as a parameter like any other parameter. the key is you have to build a dynamic sql statement, which then you should consider if it's easier to build it in your app tier or in the procs.

create procedure myProc

@tableName nvarchar(50)

as

sp_executesql N'select * from ' + @tablename

fyi this code sample is from memory have a look at BOL for the proper syntax of sp_executesql.

Also this is highly sucesptible to SQL injection as you indicated is not an issue for you but anyone reading this should be very wary of accepting input from a user to generate their queries like this.