SqlParameter doesn't work with a schema name

Question

I am attempting to use SQLParamaters to construct my SQL command. It works fine when I use them in the “where” clause but fail when used as part of the schema name. Here is the sample SQL string with the code for the problematic parameter. I’ve not shown the code for the @lastId parameter as this works and is the same as the code below.

The SQL string:

select * from [Exchequer].[@companyId].[CUSTSUPP] where acCode = @lastId

Code used to insert the companyId parameter:

var cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;

var param = cmd.CreateParameter();
param.SqlDbType = SqlDbType.Text;
param.ParameterName = "@companyId";
param.Value = companyId;
cmd.Parameters.Add(param);

var reader = cmd.ExecuteReader();

Answer 1

SQL parameter cannot apply to database / schema / table. You would probably set the schema into your SQL string manually, with care of SQL injection.

For example, check the @companyId with other query, if exists then construct the SQL.

SELECT * FROM sys.schemas WHERE name = @companyId

Answer 2

You can't use parameter as schema or table name. You have to create the query string dymamically:

strinq query = "select * from [Exchequer].[" + companyId + "].[CUSTSUPP] where acCode = @lastId";

Answer 3

You can't parameterize identifiers in sql server.
One way around it is to create dynamic sql (with all the drawbacks of that).

Another way is to create a stored procedure that will execute the select query for each schema based on the parameter you send to it.
Something like this:

create procedure SelectCUSTSUPP
(
    @SchemaName sysname,
    @acCode int
)
AS

IF @SchemaName = 'SchemaA'
    SELECT * FROM [Exchequer].[SchemaA].[CUSTSUPP] where acCode = @acCode 

IF @SchemaName = 'SchemaB'
    SELECT * FROM [Exchequer].[SchemaB].[CUSTSUPP] where acCode = @acCode 

IF @SchemaName = 'SchemaC'
    SELECT * FROM [Exchequer].[SchemaC].[CUSTSUPP] where acCode = @acCode 


GO