Let's say, My table structure and data sample as follow, ************************************************** declare @tPosiHdr TABLE ( TID int not null, BusN varchar (20) NOT NULL, TTime varchar(6) not null, strPosi varchar(6)not null, DDate datetime not null ) insert into @tPosiHdr values(1,'ADR3344','120000','00','09/01/2009') insert into @tPosiHdr values(2,'WNR4474','130000','00','09/02/2009') insert into @tPosiHdr values(3,'WKM1955','190000','00','09/04/2009') insert into @tPosiHdr values(4,'WKM1955','150000','00','10/13/2009') insert into @tPosiHdr values(5,'TXM4478','130000','00','10/16/2009') insert into @tPosiHdr values(6,'TXM1925','170000','00','11/02/2009') insert into @tPosiHdr values(7,'KTX1955','150000','00','11/09/2009') declare @tPosiDtl_200909 TABLE( TID int not null, KT varchar (20) NOT NULL, HPT varchar(6) not null ) insert into @tPosiDtl_200909 values(1,'0','1') insert into @tPosiDtl_200909 values(2,'0','1') insert into @tPosiDtl_200909 values(3,'1','2') declare @tPosiDtl_200910 TABLE( TID int not null, KT varchar (20) NOT NULL, HPT varchar(6) not null ) insert into @tPosiDtl_200910 values(4,'0','1') insert into @tPosiDtl_200910 values(5,'1','2') declare @tPosiDtl_200911 TABLE( TID int not null, KT varchar (20) NOT NULL, HPT varchar(6) not null ) insert into @tPosiDtl_200911 values(7,'1','2') insert into @tPosiDtl_200911 values(8,'0','1') insert into @tPosiDtl_200911 values(9,'1','2') ************************************************** so far, i've this, select t1.TID,t1.BusN,t1.TTime,t1.DDate, convert(varchar(4),datepart(yyyy,t1.DDate)) + right('00'+convert(varchar(2),datepart(mm,t1.DDate)),2) as whichTable, t2.KT,t2.HPT from @tPosiHdr t1 left outer join (select TID,KT,HPT from @tPosiDtl_200909)t2 on t1.TID=t2.TID my result will TID| BusN |TTime | DDate | whichTable| KT | HPT -------------------------------------------------------------------- 1 ADR3344 120000 2009-09-01 00:00:00.000 200909 0 1 2 WNR4474 130000 2009-09-02 00:00:00.000 200909 0 1 3 WKM1955 190000 2009-09-04 00:00:00.000 200909 1 2 4 WKM1955 150000 2009-10-13 00:00:00.000 200910 NULL NULL 5 TXM4478 130000 2009-10-16 00:00:00.000 200910 NULL NULL 6 TXM1925 170000 2009-11-02 00:00:00.000 200911 NULL NULL 7 KTX1955 150000 2009-11-09 00:00:00.000 200911 NULL NULL I want to make it my SQL is dynamic. The rule is -- If whichTable=200909, the join table will be on @tPosiDtl_200909 -- If whichTable=200910, the join table will be on @tPosiDtl_200910, and so on. So my result will be, TID| BusN |TTime | DDate | whichTable| KT | HPT -------------------------------------------------------------------- 1 ADR3344 120000 2009-09-01 00:00:00.000 200909 0 1 2 WNR4474 130000 2009-09-02 00:00:00.000 200909 0 1 3 WKM1955 190000 2009-09-04 00:00:00.000 200909 1 2 4 WKM1955 150000 2009-10-13 00:00:00.000 200910 0 1 5 TXM4478 130000 2009-10-16 00:00:00.000 200910 1 2 6 TXM1925 170000 2009-11-02 00:00:00.000 200911 0 1 7 KTX1955 150000 2009-11-09 00:00:00.000 200911 1 2 I think is not good to UNION ALL @tPosiDtl_200909, @tPosiDtl_200910, @tPosiDtl_200911. This way, will force the table name to be hardcoded. It's posible my SQL statement will dynamic? how my dynamic SQL looks like? DECLARE @sql as nvarchar(4000) SET @sql = ' ????' EXEC sp_executesql @sql Really need help
With dynamic SQL, you only need to elaborate on this bit: SET @sql = ' ????' T-SQL is procedural, so you can use control-of-flow stuff like IF and ELSE. You can also use variables to hold table names (but only for concatenation into a dynamic SQL statement). In line, you can use CASE - WHEN - ELSE - END to control which bits get concatenated.