SQL STRING INPUT OUTPUT VARIABLE EXEC SP_EXECUTESQL ( EXEC can only do that with SP , not string )

DECLARE @YYYYMM CHAR(6)
SELECT @YYYYMM=CONVERT(VARCHAR,YEAR(GETDATE()))+RIGHT(‘0’+CONVERT(VARCHAR,MONTH(GETDATE())),2)
PRINT @YYYYMM
DECLARE @SQL NCHAR(254)
SET @SQL=’SELECT @TBLCNTOUT=COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ”CHANNELSALESREPORT_’ + @YYYYMM + ””
PRINT @SQL
DECLARE @TBLCNT INT
EXEC sp_executesql  @SQL , N’@TBLCNTOUT INT OUTPUT’, @TBLCNTOUT = @TBLCNT OUTPUT
PRINT @TBLCNT

=====================================================================================

DECLARE @YYYYMM NVARCHAR(6)
SELECT @YYYYMM=CONVERT(NVARCHAR,YEAR(GETDATE()))+RIGHT(‘0’+CONVERT(NVARCHAR,MONTH(GETDATE())),2)
PRINT @YYYYMM
DECLARE @TBLNAME NVARCHAR(254)
SET @TBLNAME = ‘CHANNELSALESREPORT_’ + @YYYYMM
PRINT @TBLNAME
DECLARE @SQL_CHK_IMPORTED NVARCHAR(254)
SELECT @SQL_CHK_IMPORTED=’SELECT @TBLCNTOUT=COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘ + ”” + @TBLNAME  + ””
PRINT @SQL_CHK_IMPORTED
PRINT LEN(@SQL_CHK_IMPORTED)

DECLARE @TBLCNT INT
EXEC sp_executesql  @SQL_CHK_IMPORTED , N’@TBLCNTOUT INT OUTPUT’, @TBLCNTOUT = @TBLCNT OUTPUT
PRINT ‘EXISTING TABLE OF CURRENT MONTH IS ‘ + CONVERT(VARCHAR,@TBLCNT)
DECLARE @SQL_CHK_UPDATED NVARCHAR(254)
DECLARE @ISUPDATED INT
SET @ISUPDATED = 0
SET @SQL_CHK_UPDATED=’SELECT @ISUPDATEDOUT=COUNT(*) FROM OraImpLog WHERE TableName=’ + ”” + @TBLNAME + ””
PRINT @SQL_CHK_UPDATED
EXEC sp_executesql @SQL_CHK_UPDATED, N’@ISUPDATEDOUT INT OUTPUT’, @ISUPDATEDOUT = @ISUPDATED OUTPUT
PRINT ‘ISUPDATED IS ‘ + CONVERT(VARCHAR,@ISUPDATED)
IF @TBLCNT = 1 AND @ISUPDATED = 0
BEGIN
DECLARE @SQL_UPDATE NVARCHAR(254)
SET @SQL_UPDATE=’INSERT INTO CHANNELSALESREPORT SELECT * FROM ‘ + @TBLNAME + ‘ EXCEPT SELECT * FROM CHANNELSALESREPORT’
EXEC ( @SQL_UPDATE )
END