Udostępnij za pośrednictwem

How to use TSQL parameters to actually use it as an Identifier rather than the value?

  • Artykuł

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!