Dynamically include a table column names in a select?

Question

I have a select defined in a stored procedure that has a hard coded table column name in it PoliticsId

Defined as:

 [PoliticsId] [int] NOT NULL.

It holds integer values.

@a_ProfileCategoryId comes in a value = 1.

          SELECT SelectionId    
                 ,COUNT(SelectionId) AS Count
                 ,[Description]
          FROM dbo.ProfileCategoryAllSelections a
          JOIN dbo.UserProfile b ON SelectionId = PoliticsId
          WHERE ( ProfileCategoryId = @a_ProfileCategoryId )
          GROUP BY SelectionId
                   ,[Description]
          ORDER BY COUNT(SelectionId)

It works fine.

SelectionId Count   Description
1           1       Conservative
4           4       Independent

---

I now want to make it dynamic so I can pass in a similar 'column name' to it as a parameter @a_columnName.

So the parameter would contain a value of say 'PoliticsId'.

@a_ProfileCategoryId comes in a value = 4.

          SELECT SelectionId    
                         ,COUNT(SelectionId) AS Count
                         ,[Description]
          FROM dbo.ProfileCategoryAllSelections a
          JOIN dbo.UserProfile ON SelectionId = @a_columnName
          WHERE ( ProfileCategoryId = @a_ProfileCategoryId )
          GROUP BY SelectionId
                              ,[Description]
          ORDER BY COUNT(SelectionId)

How do I code for that?

---

I tried dynamic SQL and solved coding for the 'column name'. It works.

          DECLARE @sql nvarchar(MAX)
                       ,@columnName varchar(50)

          SET @columnName = 'PoliticsId'

          SELECT @sql =
                ' SELECT SelectionId    
                         ,COUNT(SelectionId) AS Count
                         ,[Description]
          FROM dbo.ProfileCategoryAllSelections a
          JOIN dbo.UserProfile ON SelectionId = '+@columnName+'
          WHERE ( ProfileCategoryId = 4)
          GROUP BY SelectionId
                              ,[Description]
          ORDER BY COUNT(SelectionId)'

        -- Execute the dynamic sql.
        EXECUTE sp_executesql @sql

---

However, now when I try to remove the hard coding of the ProfileCategoryId = 4,

          DECLARE @sql nvarchar(MAX)
                      ,@columnName varchar(50)
                      ,@ProfileCategoryId int

          SET @columnName = 'PoliticsId'
          SET @ProfileCategoryId = 4

          SELECT @sql =
                ' SELECT SelectionId    
                         ,COUNT(SelectionId) AS Count
                         ,[Description]
          FROM dbo.ProfileCategoryAllSelections a
          JOIN dbo.UserProfile ON SelectionId = '+@columnName+'
          WHERE ( ProfileCategoryId = '+@ProfileCategoryId+')
          GROUP BY SelectionId
                              ,[Description]
          ORDER BY COUNT(SelectionId)'

        -- Execute the dynamic sql.
        EXECUTE sp_executesql @sql

I get:

      Conversion failed when converting the varchar value ' SELECT SelectionId    
                         ,COUNT(SelectionId) AS Count
                         ,[Description]
          FROM dbo.ProfileCategoryAllSelections a
          JOIN dbo.UserProfile ON SelectionId = PoliticsId
          WHERE ( ProfileCategoryId = ' to data type int.

---

I tried casting.

          DECLARE @sql nvarchar(MAX)
                       ,@columnName varchar(50)
                       ,@ProfileCategoryId int
                       ,@CastedProfileCategoryId varchar(MAX)

          SET @columnName = 'PoliticsId'
          SET @ProfileCategoryId = 4

          SELECT @CastedProfileCategoryId = CAST(@ProfileCategoryId AS VARCHAR)

          SELECT @sql =
                ' SELECT SelectionId    
                ,COUNT(SelectionId) AS Count
                ,[Description]
          FROM dbo.ProfileCategoryAllSelections a
          JOIN dbo.UserProfile ON SelectionId = '+@columnName+'
          WHERE ( ProfileCategoryId = @CastedProfileCategoryId )
          GROUP BY SelectionId
                              ,[Description]
          ORDER BY COUNT(SelectionId)'

        -- Execute the dynamic sql.
        EXECUTE sp_executesql @sql

I get:

Must declare the scalar variable "@CastedProfileCategoryId".

Answer 1

You need dynamic SQL for this:

      DECLARE @sql nvarchar(MAX)
              ,@columnName varchar(50)
              ,@ProfileCategoryId int

      SET @columnName = 'PoliticsId'
      SET @ProfileCategoryId = 4

      SELECT @sql =
            ' SELECT SelectionId    
            ,COUNT(SelectionId) AS Count
            ,[Description]
      FROM dbo.ProfileCategoryAllSelections a
      JOIN dbo.UserProfile ON SelectionId = '+ CAST(@ProfileCategoryId AS VARCHAR) +'
      WHERE ( ProfileCategoryId = @CastedProfileCategoryId )
      GROUP BY SelectionId
                          ,[Description]
      ORDER BY COUNT(SelectionId)'

    -- Execute the dynamic sql.
    EXECUTE sp_executesql @sql