Of course, I'd do this task like below. Be warned though, that I don't have to worry about sql injection in my closed environment.

CREATE PROCEDURE [dbo].[spDmValidationCheckTODD](

@DVTableName NVARCHAR(101)

,@DataMartTableName NVARCHAR(101)

,@debug bit = 0)

AS

DECLARE @SQL VARCHAR(MAX)

SET @SQL = '

INSERT INTO DmValidation(DVTableName, DataMartTableName, DVTableRecordCount, DataMartTableRecordCount, LogDateTime, RemarksAction)

SELECT

' + '''' + @DVTableName + '''' + ', ' + '''' + @DataMartTableName + '''' + ',

(SELECT COUNT(*) FROM ' + @DVTableName + '),

(SELECT COUNT(*) FROM ' + @DataMartTableName + '),

GETDATE(), ''''

'

IF @debug = 1 PRINT @SQL

EXEC(@SQL)