Need help on Dynamic SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need help on Dynamic SQL

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2009-11-09 : 07:51:24

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

It's possible my SQL will be dynamic?

If yes, I'm stuck to make it my SQL statement dynamic?
DECLARE @sql as nvarchar(4000)
SET @sql = ' ????'
EXEC sp_executesql @sql

Really need help

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-09 : 14:19:49

Do you really have tables by year?

You could create 1 table with the column for the year

Are you saddled with the design you put forth?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2009-11-09 : 17:47:24

quote:
Originally posted by X002548

Do you really have tables by year?

You could create 1 table with the column for the year

Are you saddled with the design you put forth?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Do you really have tables by year -- Yes. I've table by year+month
You could create 1 table with the column for the year -- How? Can you elaborate?
Are you saddled with the design you put forth? -- This table design from legacy system.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-09 : 18:18:43

So how does this legacy system work with all of these year/month tables

How many do you have?

Do they have a naming standard?

Let's assume, it's

tPosiDtl_ccyymm

k?

And let's further assume that they all have the same columns (crazy, I know)

Then Create a view

CREATE VIEW tPosiDtl
AS
SELECT 2009 AS Rosi_Year
, 9 AS Rosi_Month
, KT
,HPT
FROM tPosiDtl_200909
UNION ALL
SELECT 2009 AS Rosi_Year
, 10 AS Rosi_Month
, KT
,HPT
FROM tPosiDtl_200909
UNION ALL
ect

Then you could just select against this one table

Typing it all out would take a bit...but you could probably use the cat to gen the view...

Do you think this would help?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-09 : 19:14:27

here...this will generate a view from all your tables...just lose the last UNION ALL at the end



SELECT SQL FROM (
SELECT TOP 1 'CREATE VIEW   ' + SUBSTRING (TABLE_NAME,1,8) + ' AS ' AS SQL
, TABLE_NAME, 1 AS ORDINAL_POSITION, 1 AS SQL_GROUP
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'tPosiDtl%'
UNION ALL
SELECT  '     SELECT   ' 
	+ SUBSTRING(TABLE_NAME, 10,4) + ' AS Dtl_Year,  '
	+ SUBSTRING(TABLE_NAME, 14,2) + ' AS Dtl_Month, '
AS SQL
, TABLE_NAME,  1 AS ORDINAL_POSITION, 2 AS SQL_GROUP
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME LIKE 'tPosiDtl%'
AND ORDINAL_POSITION = 1
UNION ALL
SELECT  '           ,  ' + COLUMN_NAME AS SQL
, TABLE_NAME, ORDINAL_POSITION, 2 AS SQL_GROUP
FROM INFORMATION_SCHEMA.Columns
WHERE TABLE_NAME LIKE 'tPosiDtl%'
UNION ALL
SELECT DISTINCT '       FROM   ' + TABLE_NAME + CHAR(13) + CHAR(10) 
			  + '  UNION ALL ' AS SQL
, TABLE_NAME, 4 AS ORDINAL_POSITION, 3 AS SQL_GROUP
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'tPosiDtl%'
) AS XXX
ORDER BY TABLE_NAME, SQL_GROUP, ORDINAL_POSITION

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2009-11-09 : 21:58:32

Let's refresh, my table not table variables anymore
Let's say, My table structure and data sample as follow,
**************************************************
CREATE TABLE tPosiHdr (
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')

CREATE TABLE tPosiDtl_200909 (
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')

CREATE TABLE tPosiDtl_200910 (
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')

CREATE TABLE tPosiDtl_200911 (
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

It's possible my SQL will be dynamic?

I'm thinking of using @tblTemp and loop of While End.

DECLARE @tblTemp TABLE (
tid INT,
BusN VARCHAR(20),
TTime VARCHAR(6),
DDate DATETIME,
whichTable VARCHAR(6),
KT VARCHAR(20),
HPT VARCHAR(6)
)

WHILE .....
i dont know how to get @whichTable here ...

select @sql = 'insert into @tblTemp
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_'+@whichTable+'
)t2
on t1.TID=t2.TID

EXEC (@sql)
.....
.....
END

SELECT * FROM @tblTemp

Can help?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources