SQL use variable in column name

1

I need to use variables to define the column name in my sql query.

Gender:

SELECT        IDLayout, Nome, @collum, TipoProduto
FROM            ProdutoLayout
WHERE        (TipoProduto = @tipo) AND (@collum = 1) 

Since @collum is the variable where the column name is located.

    
asked by anonymous 27.03.2014 / 14:15

3 answers

4

You will not be able to change the name of the column by SqlCommand , since the parameters only serve to add values.

You will have to do this before creating the command by changing the original SQL:

var valorTipo = "valor do tipo";
var nomeDaColuna = "NomeColuna";
var sql = string.Format(@"
   SELECT     IDLayout, Nome, {0}, TipoProduto
   FROM       ProdutoLayout
   WHERE      (TipoProduto = @tipo) AND ({0} = 1) 
", nomeDaColuna);

var command = new SqlCommand(sql);
command.Parameters.AddWithValue("@tipo", valorTipo);

I hope the name of this column comes from a trusted source, as this will be a SQLInjection point and it will have to be handled if it is the case.

    
27.03.2014 / 14:45
3

Using SQL, you have to use EXEC:

declare @sql varchar(max)

set @sql = 'SELECT        IDLayout, Nome, '+@collum+', TipoProduto
FROM            ProdutoLayout with(nolock)
WHERE        (TipoProduto = '+@tipo+') AND ('+@collum+' = 1) '
exec(@sql)

Regarding variables at runtime, only this way.

[]'s

    
04.04.2014 / 21:10
0

Just add parameters with the same name as the variables in your query. Example:

var connect = new SqlCommand("String de conexao");
            var command = new SqlCommand("SELECT IDLayout, Nome, @collum, TipoProdutoFROM ProdutoLayout WHERE (TipoProduto = @tipo) AND (@collum = 1) ");
            command.Parameters.AddWithValue("@collum", "valor");
            command.Parameters.AddWithValue("@tipo", "valor");
    
27.03.2014 / 14:24