You can't use a variable to refer to a table name unless you use dynamic SQL. If you go down that route, make sure you have validated the table names very carefully to avoid
SQL Injection[
^] vulnerabilities.
Declare @LONDATE As char(11) = 'LON20190629';
Declare @ADVDATE As char(11) = 'ADV20190629';
Declare @LonDateTable As sysname;
Declare @AdvDateTable As sysname;
Declare @query nvarchar(max);
SELECT @LonDateTable = name
FROM MIS.sys.tables
WHERE SCHEMA_NAME(schema_id) = 'dbo'
And name = @LONDATE;
SELECT @AdvDateTable = name
FROM MIS.sys.tables
WHERE SCHEMA_NAME(schema_id) = 'dbo'
And name = @ADVDATE;
SET @query = N'SELECT l.BrCode, l.Code, CONCAT(l.BRCODE, l.CODE, RIGHT(''000000'' + CAST(l.Acno AS VARCHAR(6)), 6)) AS ACNO, c.name, c.PAN, l.CustNo, l.LIMITAMT, l.SECVALUE, (l.ClearBal + l.AccumuInt + l.clgamt) AS BALANCE, l.NPACode, l.NPADt, l.OverdueAmt, l.UnreserveInt, l.INTIND7AMT, l.PENALINTIND7AMT, l.AcSts, l.Limit, l.DueInst, l.InstAmt FROM MIS.dbo.' + QuoteName(@LonDateTable) + N' As l INNER JOIN MIS.dbo.customer c ON l.custno = c.Code WHERE AcSts != 9 AND ClearBal < 0
UNION ALL
SELECT a.BrCode, a.Code, CONCAT(a.BRCODE, a.CODE, RIGHT(''000000'' + CAST(a.Acno AS VARCHAR(6)), 6)) AS ACNO, c.Name, c.PAN, a.CustNo, a.LIMITAMT, a.SECVALUE, (a.ClearBal + a.AccumuInt + a.clgamt) AS BALANCE, a.NPACode, a.NPADt, a.OverdueAmt, a.UnreserveInt, a.INTIND7AMT, a.PENALINTIND7AMT, a.AcSts, a.Limit, ''0'' as DueInst, ''0'' as InstAmt FROM MIS.dbo.' + QuoteName(@AdvDateTable) + N' a INNER JOIN MIS.dbo.customer c ON a.custno = c.Code WHERE AcSts != 9 AND ClearBal < 0';
EXEC sp_executesql @query;
sp_executesql (Transact-SQL) - SQL Server | Microsoft Docs[
^]
NB: This seems like a poor database design. There should be a single
LON
table and a single
ADV
table, with the relevant date stored against each record. If you're worried about the size of the data, you can use a partitioned table:
Partitioned Tables and Indexes - SQL Server | Microsoft Docs[
^]