Click here to Skip to main content
15,846,976 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more: , +
Hello coders, I want to run below mentioned query from one table to another, also table name keeps on changing i have to declare them, that's where i stuck.

Please guide me.

What I have tried:

DeclArE @LONDATE As chAr(11)='LON20190629'
DeclArE @ADVDATE As chAr(11)='ADV20190629'
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.@LONDATE l ,MIS.dbo.customer c where l.custno=c.Code and 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' asInstAmt FROM MIS.dbo.@ADVDATE a,MIS.dbo.customer c where a.custno=c.Code and AcSts<>9 AND ClearBal<0
Posted
Updated 6-Jul-20 2:37am
Comments
DerekT-P 6-Jul-20 6:23am    
I think you need to simplify your query so we can see what you're trying to do. The question title refers to "one DB to another using variable" but your SQL join only involves a single database: MIS.dbo - where's the other database?
I *think* your issue is that you need to join two tables (or even access just one of them) but the table name you need to query is date-dependent and therefore you need to parameterise the table name - is that correct? Please confirm and, if that's the case, someone here will be able to help you.
Hemil Gandhi 7-Jul-20 6:51am    
Got the answer, thanks for the trying though
Hemil Gandhi 8-Jul-20 7:01am    
In above table NPADt is date having three types of data 0,null or date i.e (20200807). How to show date in DD-MM-YYYY format??
DerekT-P 9-Jul-20 5:04am    
You're best asking a new question in, um, a new question; especially as this one is already flagged as solved. (For one thing, other people will then be able to search and find a solution if they have a similar problem)

1 solution

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.
SQL
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[^]
 
Share this answer
 
v2
Comments
Hemil Gandhi 7-Jul-20 6:51am    
Thank you very much buddy you saved my lots of effort....
Hemil Gandhi 8-Jul-20 7:01am    
In above table NPADt is date having three types of data 0,null or date i.e (20200807). How to show date in DD-MM-YYYY format??
Richard Deeming 8-Jul-20 7:04am    
Convert it to the date type in your query, and format it in your UI code.
CASE WHEN a.NPADt Is Null THEN Null WHEN a.NPADt = 0 THEN Null ELSE Convert(date, Convert(char(8), a.NPADt), 112) END As NPADt

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900