Using variable as fieldname in SELECT statement

elygp · April 23rd, 2007, 09:32 PM

Hi,

Is there a ways to use a variable as fieldname using SQL statement?

Exampl:

  Instead of :

SELECT empname FROM emptable

   I would like to use a varible for the fieldname. the varialbe contains the fieldname i want to select i.e.:

SELECT myvariable FROM emptable

   where myvariable contains a fieldname of the table emptable.

   I remember using dbase III+, this can be done using macro (&) character. Unfortunately I can't make it work in SQL 2000.

Thanks for your help.

joefawcett · April 24th, 2007, 12:13 AM

You can use dynamic SQL:

Code:

DECLARE @ColumnName SYSNAME;
SET @ColumnName = 'MyColumn';
DECLARE @SQL NVARCHAR(1000);
SET @SQL = 'SELECT ' + @ColumnName + ' FROM tblMyTable;';
EXEC(@SQL);

Alternatively, instead of using EXEC use sp_ExecuteSql which maybe better for performance.

See http://www.sqlteam.com/item.asp?ItemID=4619.

--

Joe (Microsoft MVP - XML)

elygp · April 24th, 2007, 02:23 AM

Thanks for the reply Joe. I tried the code and put it in a stored procedure and it worked. However, I cannot combine it with WHERE clause. I tried using parametarized stored proc and it returns an error. The stored proc goes like this:

ALTER PROCEDURE dbo.myStoredProc

      (

        @prmproject_id nvarchar(50),
       @prmfieldname nvarchar(50),
     @prmaverage nvarchar(50)
   )

    AS

DECLARE @mySQL nvarchar(1000)
DECLARE @columnname sysname

SET @columnname=@prmfieldname

SET @mySQL = 'SELECT ' + @columnname + ' FROM CompletionRateAverage '

     EXECUTE(@mySQL)

This works fine.

But when I tried combining WHERE clause as in:

SET @mySQL = 'SELECT ' + @columnname + ' FROM CompletionRateAverage ' + 'WHERE project_id = @prmproject_id'

Must declare the variable '@project_id' error is returned.

When I tried to change the statement to:

SET @mySQL = 'SELECT ' + @columnname + ' FROM CompletionRateAverage ' + 'WHERE project_id ='+ @prmproject_id

Invalid Column Name <value of project_id> is returned. As if the value of the project_id field is treated as column name.

Please help... Thanks!

happygv · April 26th, 2007, 10:14 AM

Code:

SET @mySQL = 'SELECT ' + @columnname + ' FROM CompletionRateAverage ' + 'WHERE project_id = ' + @prmproject_id
-- Try a PRINT next to this and copy paste the result of PRINT in query analyser,
-- see if that works, else post here your observations.
-- EXECUTE(@mySQL)
Print @mySQL

Cheers.

_________________________
- Vijay G
Strive for Perfection