CODEWITHSUNDEEP
c#sqlparameters
morganpdx
morganpdx

Reputation: 1876

Can you use a SQLParameter in the SQL FROM statement?

I am trying to create a parameterized query in C# against a SQL server database.

Code:

query = new StringBuilder( "SELECT @fields FROM @tables");

using(SqlConnection connection = new SqlConnection(connection))
{
    SqlCommand command = new SqlCommand(query.ToString(), connection);
    command.Parameters.AddWithValue("@fields", fields.ToString());
    command.Parameters.AddWithValue("@tables", tables.ToString());

    try
    {
        connection.Open();
        Int32 rowsAffected = command.ExecuteNonQuery();
        Console.WriteLine("RowsAffected: {0}", rowsAffected);
    }
    catch(Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

The strange part is this fails with the message "Must declare the table variable "@tables". However as you can see, it's clearly been defined.

So my question is:

  1. Can you pass a parameter to define the table list in the FROM statement?
  2. If you can, why isn't this working?

Upvotes: 3

Views: 2195

Answers (4)

Joe Enos
Joe Enos

Reputation: 40393

If you're confident that your table and column names are ok, then you can do some safety checks in the database before building your dynamic SQL.

This is just for illustration - for real life, obviously you'd need to make it a lot cleaner:

declare @TABLE_NAME nvarchar(128)
set @TABLE_NAME = 'Robert'');DROP TABLE Students;--' -- This line will raise an error
set @TABLE_NAME = 'BOOK' -- This line will go through properly

declare @sql varchar(max)
set @sql = 'SELECT * FROM '

if exists (select 1 from sys.objects where type = 'U' and name = @TABLE_NAME)
    begin
        set @sql = @sql + @TABLE_NAME
        exec (@sql)
    end
else
    begin
        raiserror ('ERROR ERROR ERROR', 0, 0)
        return
    end

Upvotes: 0

Ondrej Vencovsky
Ondrej Vencovsky

Reputation: 3498

I think this is not the way SQL command and its parameters should look like. It should look like

SELECT fieldName1, fieldName2
FROM   TableName
WHERE  fieldName = @paramName

You cannot use parameters as definition of fields to be selected or the target table. If you need to define fields to be selected, simply compose the command string in StringBuilder before you call it - as you need. Parameters are used for filtering purposes. In your case you don't need any paramters, just build your command and execute.

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332531

SQL doesn't support the FROM clause to be parameterized. So you have to use either dynamic SQL, or create/concatenate the query string prior to submitting it to the database.

Upvotes: 7

JK.
JK.

Reputation: 21809

No unfortunately you cant use a parameter in the FROM clause.

Upvotes: 2

Related Questions