CREATE PROCEDURE  [dbo].[spDmValidationCheck](
	                                 @DVTableName NVARCHAR(101)
	                                ,@DataMartTableName NVARCHAR(101)
	                                ,@DVTableRecordCount BIGINT OUTPUT 
							        ,@DataMartTableRecordCount BIGINT OUTPUT
							        )				        

	
	AS 
	BEGIN
    
    DECLARE @CurrentDateTime DATETIME=GETDATE()
	DECLARE @SQL NVARCHAR(MAX)='SELECT COUNT (*) FROM '
	DECLARE @DmSQL NVARCHAR(MAX)=''
	DECLARE @DvSQL  NVARCHAR(MAX)=''

	SET @DmSQL=@SQL+@DataMartTableName	
	EXEC @DVTableRecordCount = sp_executesql @DmSQL OUTPUT ---??
	
	INSERT INTO Test.dbo.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

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

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)