Parameter Passing - Table Name & Column Name In Stored Procedures

How can a Stored Procedure use a variable table name and column name ?

The statement :-

SELECT @columnname FROM @tablename

gives error "Line 5: Incorrect syntax near '@tablename'."

I am passing the parameters 'tablename' and 'columnname' into the stored procedure, (in order to select a variable column from a variable table).

If I hard-code the tablename, then I get a column of output with just the name of the column I was trying to list, e.g. surname,

i.e. SELECT @columnname FROM employeetable gives :-

surname
surname
surname
surname
surname
surname


How can I get the procedure to accept a variable table name and column name ?


I thought one way to do it would be to use the 'EXEC' statement :-

SELECT @sql = 'SELECT '+ @columnname +' FROM '+@tablename
EXEC(@sql)

but this gives error : "Incorrect syntax near the keyword 'FROM' "

although the following table works :-

SELECT @sql = 'SELECT surname FROM '+@tablename
EXEC(@sql)

The problem seems to be mainly with variable column names.

Also, we don't really want to use the EXEC statement because it will not be compiled and will be slower. (Does anyone know by how much slower ?)


Any advice would be appreciated.

Kind regards,
Ian.
(ian.mitchell@sds.no)