SQL use variable in column name

Question

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

Gender:

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

Whereas the @collum is the variable where the column name is.

Answer 1

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

You need 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 that the name of this column comes from a reliable source, because this will be a point of Sqlinjection and it will have to be treated if the case.

Answer 2

Via SQL itself, you must 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)

In the case of variables at runtime, only in this way.

[]s

Answer 3

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");