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!