SQL Server Performance

Need help on dynamic SQL

Discussion in 'T-SQL Performance Tuning for Developers' started by wkm1925, Nov 9, 2009.

  1. wkm1925 New Member

    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
  2. Adriaan New Member

    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.
  3. kishore_pen New Member

    Table variables not supported in dynamic SQL, use temp. tables for more info SQL BOL.

Share This Page