How to pass table name and column name dynamic in SQL

I was trying to pass table name and column name dynamic, this is as part of SSIS process I am trying this stored procedure below.

CREATE PROCEDURE [lnd].[Get_ANCNotullColumn]
    (@PassedTableName AS NVarchar(255),
     @PassedColumnName AS NVARCHAR(100)) 
AS
BEGIN  
    DECLARE @ActualTableName AS NVarchar(255)

    SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = @PassedTableName

    DECLARE @sql AS NVARCHAR(MAX)

    SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ';'

    DECLARE @final AS NVARCHAR(MAX)

    SELECT @final = @sql + 'WHERE ' + @PassedColumnName + ' IS NULL  OR ' + @PassedColumnName + '='''

    EXEC(@SQL)
END

On executing this, I am NOT getting count as result, instead I am getting execution success.

EXEC [lnd].[Get_ANCNotullColumn] 'lnd.ANC_LND_ItemOverride', 'comments' 

I need to get the count as output.

Also my simple direct query is like this

SELECT COUNT(*) 
FROM lnd.ANC_LND_ItemOverride  
WHERE Comments IS NULL OR Comments = '' -- 3 is the output

Answer

I think you may need to modify you value passing and your concatenation values.

from this statement you need to remove the semi colon as it will throw error

SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ';'

While passing blank values you need additional quotes

SELECT @final = @sql + 'WHERE ' + @PassedColumnName + ' IS NULL  OR ' + @PassedColumnName + '= '''''

While execution I believe you wanted to execute final instead of SQL

I think below should give your output:

CREATE PROC [lnd].[Get_ANCNotullColumn]( @PassedTableName as NVarchar(255),@PassedColumnName AS 
     NVARCHAR(100)) 
 AS
 BEGIN
 DECLARE @ActualTableName AS NVarchar(255)

SELECT @ActualTableName = QUOTENAME( TABLE_NAME )
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @PassedTableName

DECLARE @sql AS NVARCHAR(MAX)
SELECT @sql = 'SELECT COUNT(*) FROM ' + @ActualTableName + ' '

DECLARE @final AS NVARCHAR(MAX)
SELECT @final = @sql + 'WHERE ' + @PassedColumnName + ' IS NULL  OR ' + @PassedColumnName + '='''''

EXEC(@final)
END