To make what you have work, take a look a this:

CREATE PROCEDURE [dbo].[spDmValidationCheck](

@DVTableName NVARCHAR(101)

,@DataMartTableName NVARCHAR(101)

,@DVTableRecordCount BIGINT OUTPUT

,@DataMartTableRecordCount BIGINT OUTPUT

)

AS

BEGIN

DECLARE @CurrentDateTime DATETIME=GETDATE()

DECLARE @SQL1 NVARCHAR(MAX)=N'SELECT @DVTableRecordCount = COUNT(*) FROM '

DECLARE @SQL2 NVARCHAR(MAX)=N'SELECT @DataMartTableRecordCount = COUNT(*) FROM '

DECLARE @DmSQL NVARCHAR(MAX)=''

DECLARE @DVSQL NVARCHAR(MAX)=''

SET @DmSQL=@SQL1+@DVTableName

EXEC sp_executesql @DmSQL, N'@DVTableRecordCount BIGINT OUTPUT', @DVTableRecordCount OUTPUT

SET @DmSQL=@SQL2+@DataMartTableName

EXEC sp_executesql @DmSQL, N'@DataMartTableRecordCount BIGINT OUTPUT', @DataMartTableRecordCount OUTPUT

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

VALUES (@DVTableName,@DataMartTableName,@DVTableRecordCount,@DataMartTableRecordCount,@CurrentDateTime,NULL)

SELECT ISNULL(@DataMartTableRecordCount,0)AS DataMartTableRecordCount,ISNULL(@DVTableRecordCount,0) AS DVTableRecordCount

END