Nested Loops in SQL Query values from table/set

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)

Nested Loops in SQL Query values from table/set

Author

Topic

miamikk
Starting Member

19 Posts

Posted - 2007-08-30 : 13:38:26

I am a newbie SQL programmer. I need to know the syntax/example for using nested loops with condition values fetched from a table/group of fixed values etc.

The SQL Query needs creates an XML file for each combination of 3 parameters District, Month, RepType.

District values need be fetched from a table “district” and column “dist_code”. The dist_code is of type “tinyint” and its value is between 1-70.

Month values can be either fetched from a table or a fixed set. Its value is from 1-12. The table is “month” and the column name is “stat_month”. The stat_month is of type char(2).

RepType values are 1-11 need to be fetched from a fixed set.

The SQL query is below (with hard coded parameter values)
*************Begin Code************
USE test
GO

Declare @TblName1 varchar(10)
Declare @District varchar(6)
Declare @Month varchar(3)
Declare @RepType varchar(2)

SET @TblName1 = '2006exp'
SET @District = 21
SET @Month = 6
SET @RepType = 2

Declare @fName varchar(50)
SET @fName = 'C:\XMLDump\'+@TblName1+'-'+@District+'-'+@Month+'-'+@Reptype+'.xml'

SET @TblName1 = '['+@TblName1+']'

Declare @SQuery nvarchar(3000)

Set @SQuery = 'bcp "EXEC test..free_CustomsDistrict_HS4_TEST '+@TblName1+', '+@District+', '+@Month+', '+@RepType+'" QueryOut "' + @fName + '" -c -T -r -t'

Exec master..xp_cmdshell @SQuery
************End Code*************
When I execute this query, it creates a file “2006exp-21-6-2.xml”.

The need the query to create 46x12x11 = 6072 files for all the combinations of 3 parameters.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 14:18:06

[code]DECLARE @Combinations TABLE
(
RowID INT IDENTITY(1, 1),
DistCode INT,
MonthValue INT,
RepType INT
)

INSERT @Combinations
(
DistCode,
MonthValue,
RepType
)
SELECT v1.Number,
v2.Number,
v3.Number
FROM master..spt_values AS v1
CROSS JOIN master..spt_values AS v2
CROSS JOIN master..spt_values AS v3
WHERE v1.Type = 'p'
AND v1.Number BETWEEN 1 AND 70
AND v2.Type = 'p'
AND v2.Number BETWEEN 1 AND 12
AND v3.Type = 'p'
AND v3.Number BETWEEN 1 AND 11

DECLARE @TableName VARCHAR(10),
@FileName VARCHAR(100),
@RowID INT,
@DistCode VARCHAR(2),
@MonthValue VARCHAR(2),
@RepType VARCHAR(2),
@SYS VARCHAR(3000)

SELECT @RowID = MAX(RowID),
@TableName = '2006exp'
FROM @Combinations

WHILE @RowID > 0
BEGIN
SELECT @DistCode = CONVERT(VARCHAR, DistCode),
@MonthValue = CONVERT(VARCHAR, MonthValue),
@RepType = CONVERT(VARCHAR, RepType)
FROM @Combinations
WHERE RowID = @RowID

SELECT @FileName = 'C:\XMLDump\' + @TableName + '-' + @DistCode + '-' + @MonthValue + '-' + @RepType + '.xml',
@SYS = 'bcp "EXEC test..free_CustomsDistrict_HS4_TEST ' + QUOTENAME(@TableName) + ', ' + @DistCode + ', ' + @MonthValue + ', ' + @RepType + '" QueryOut "' + @FileName + '" -c -T -r -t'

EXEC master..xp_cmdshell @SYS

SET @RowID = @RowID - 1
END[/code]

E 12°55'05.25"
N 56°04'39.16"

EDIT: MISSING COMMA AND COLUMN DEFINITIONS

miamikk
Starting Member

19 Posts

Posted - 2007-08-30 : 15:08:24

I really appreciate your valuable time for helping. I parsed the code and it give these errors.

Msg 173, Level 15, State 1, Line 4
The definition for column 'DistCode' must include a data type.
Msg 102, Level 15, State 1, Line 34
Incorrect syntax near '@SYS'.
Msg 137, Level 15, State 1, Line 44
Must declare the scalar variable "@RepType".
Msg 137, Level 15, State 2, Line 48
Must declare the scalar variable "@RepType".
Msg 137, Level 15, State 2, Line 51
Must declare the scalar variable "@SYS".

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 15:34:53

See edited response above.
Not that hard to fix.

E 12°55'05.25"
N 56°04'39.16"

miamikk
Starting Member

19 Posts

Posted - 2007-08-30 : 15:37:39

I was able to figure it out.

One more question is, the District value is between 1 and 70 (total 46 values only). Its not sequential.
(1,2,4,5,7,9,10,11,13,14,15,16,17,18,19,20,21,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,41,45,46,47,49,51,52,53,54,55,60,70). With this code, I wil be having about 35% more tables. Also the Results window throws error for every district that does not exist in the database.

quote:
Originally posted by Peso

See edited response above.
Not that hard to fix.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 16:23:05

[code]INSERT @Combinations
(
DistCode,
MonthValue,
RepType
)
SELECT v1.<DistCode>,
v2.Number,
v3.Number
FROM tblDistricts AS v1
CROSS JOIN master..spt_values AS v2
CROSS JOIN master..spt_values AS v3
WHERE AND v2.Type = 'p'
AND v2.Number BETWEEN 1 AND 12
AND v3.Type = 'p'
AND v3.Number BETWEEN 1 AND 11[/code]

E 12°55'05.25"
N 56°04'39.16"

miamikk
Starting Member

19 Posts

Posted - 2007-08-30 : 17:16:54

Dear Peso,
Thank you very much for all your help. Your code work perfectly.
I have a question about using these XML files.

I am trying to convert a live reporting website (http://www.ustrade.fiu.edu/CustomsDistrict_Free.aspx) to static website since the server thats hosting the site is very old and slow. I will use the SQL query to create all possible combinations of reports so on the new system I will just pull the static report from the webserver for the any selected combination of report.

My question is can have a table on the front end display XML data in the static files like in Live report table ? Do I need to attaching any XSL stylesheet to the XML documents before I can use them ?

I would really appreciate if you can help me with this one last problem.

Thanks
KK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources