This morning, someone asked that he was having difficulty getting dynamic count on column when Column Name is passed as parameter. Ahha...! Looks like he ran into same issue, once I came across.

Problem:
How to get a dynamic count of a column by passing a Column Name as a parameter in TSQL?

Example:
SELECT COUNT(@ColumnName) FROM FOO

The above statement fails, as the parameter is not actually used as an Identifier.

Solution:
In TSQL, there is built-in stored procedure sp_executesql. This helps execute TSQL at runtime. Following TSQL code sample shows how to build a dynamic SQL to use parameter as an identifier.

Code Sample:

CREATE PROCEDURE [dbo].[TestDynamicCount]

        @TableName NVarchar(128) = 'dbo.Authors',

        @ColName NVarchar(128) = 'Name',

        @retVal int = -1 OUTPUT  

AS

DECLARE @OriginalCnt int,

@ParmDefinition nvarchar(200),

@sSQL nvarchar(200)

SET @ParmDefinition =N'@Cnt1_Out int OUT'

SET @sSQL= N'SELECT @Cnt1_Out=count('+@ColName+') FROM ' +  @TableName ;

EXEC sp_executesql @sSQL, @ParmDefinition, @OriginalCnt Out;

print @OriginalCnt

SET @retVal=@OriginalCnt

How to execute

USE [Pubs]

GO

DECLARE @retVal int

EXEC   [dbo].[TestDynamicCount]

       @TableName = N'dbo.Authors',

       @ColumnName = N'dbo.Name',  

       @retVal = @retVal OUTPUT

SELECT @retVal as N'@retVal'

GO

In above example, @ColName is used to create concatenated SQL string which is then passed to sp_executesql to dynamically build TSQL query. Also note that second parameter @ParmDefinition contains the definitions of all parameters that have been embedded in stmt.

Cheers!