Can sqlcommand parameters provide the names of database columns?

Question

Like this:

select * from foo where @nameofdbcolumnprovidedbyparam = 1 ?

Whenever I attempt this with sqldatasource or in ado I get errors such as:

Syntax error converting the nvarchar value 'foo' to a column of data type int.

Answer 1

I think in this case you would need to generate the SQL dynamically without parameter binding. Parameter binding is generally for data-input sanitation and not for customizing query structure.

-- edit --

this logic might fit your needs

SELECT *
FROM Line
WHERE ( @foo = 'monTotal' AND monTotal IS NULL)
    OR (@foo = 'monUnitPrice' AND monUnitPrice IS NULL)

Answer 2

Change your SQL to (in your code):

 string sqlToExecute = "EXEC ('SELECT * from foo WHERE ' + @nameofdbcolumnprovidedbyparam + ' =1')";

Now Sql Server will execute your query and substitute in the parameter into the SQL, so if you passed it in as "Column1", the actual Sql executed would be:

 "EXEC('SELECT * from foo WHERE Column1 =1')";

The "EXEC('')" bit allows Sql to execute a piece of dynamically constructed Sql

---

This isn't ideal as it leaves you more vulnerable to Sql Injection attacks which are not good, so it would be better if you could construct the Sql to be executed by limiting the number of selectable columns to a controlled list and switching the statement being executed depending on this. If that's not possible then the other solution would be to use a Stored Procedure that takes nullable parameters, such as:

CREATE PROCEDURE dbo.MyProc
 @param1 VARCHAR(10),
 @param2 VARCHAR(10)
 @param3 VARCHAR(10)
AS
BEGIN
SELECT *
FROM dbo.foo
WHERE ISNULL(@param1, column1) = column1
AND ISNULL(@param2, column2) = column2
AND ISNULL(@param3, column3) = column3
END

By calling this procedure and passing in NULL for the columns that you don't care about, only records from the table "foo" that match on the column you're after will be returned.

Answer 3

You can do it with a case

WHERE ( 
CASE @macolumn 
WHEN 'toto' THEN toto
WHEN 'tutu' THEN tutu
WHEN 'titi' THEN titi
ELSE null END
) = 1

It's a little bite ugly but it works and you don't have to do dynamic sql.