SQL 2000 – error with UNION in dynamic SQL – Need this one quickly PLEASE (it’s 2/16/2010) | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL 2000 – error with UNION in dynamic SQL – Need this one quickly PLEASE (it’s 2/16/2010)

Hi there! My 5yrs of SQL experience was mostly doing selects and joins in order to build datamarts for easier reporting. My specialty is with FOCUS and Dialogue Manager where it’s so easy to write dynamic code so that one program can do the work of many. My current issue is something that I wrote that will sum various counts for 4 different categories/levels of totals. Each "level’ requires an additional "group by" object. When I use the PRINT command, it produces the expected output just fine. If I take that output and put it in another query and execute, it works fine;
If I remove the UNION and run as EXEC stmt, it works fine but groups everything where I need this to be put as one output, eventually to be stored into a table which is phase2 once I can get this to work. HERE IS MY CODE:
–declare and set initial value of loop variable
DECLARE @loopvar1 int
SET @loopvar1 = 1 –stmt1 is the initial select statement
DECLARE @sqlSTMT1 NVARCHAR(4000)
SET @sqlSTMT1 = N’SELECT run_date
,end_date_requested
‘ –alliance does NOT change so not bothering to add to loop and take up unnecessary runtime
DECLARE @fld_alliance NVARCHAR(4000) —-having blank will advance to next line to keep sql neat
SET @fld_alliance =N’
,cast(Alliance as varchar(20)) as Alliance’ DECLARE @fld_allianceNM NVARCHAR(4000)
SET @fld_allianceNM =N’
,AllianceNM’ –the rest that would go in this order are within the loop.
DECLARE @sqlSTMT2 NVARCHAR(4000)
SET @sqlSTMT2 = N’
–memcntshere
,sum(d1.EEcnt) as EEcnt
,sum(d1.SPcnt) as SPcnt
,sum(d1.CHcnt) as CHcnt
,sum(d1.DEPScnt) as DEPScnt
,sum(d1.TOTcnt) as TOTcnt –webreghere
,sum(d1.EEcnt_webReg) as EEcnt_WebReg
,sum(d1.SPcnt_webReg) as SPcnt_WebReg
,sum(d1.CHcnt_webReg) as CHcnt_WebReg
,sum(d1.DEPScnt_webReg) as DEPScnt_WebReg
,sum(d1.TOTcnt_webReg) as TOTcnt_WebReg –hrahere
,sum(d1.EEcnt_HRA) as EEcnt_HRA
,sum(d1.SPcnt_HRA) as SPcnt_HRA
,sum(d1.CHcnt_HRA) as CHcnt_HRA
,sum(d1.DEPScnt_HRA) as DEPScnt_HRA
,sum(d1.TOTcnt_HRA) as TOTcnt_HRA
–EE & SPs Age ranges
,sum(d1.EESP_AGElt20) as EESP_AGElt20
,sum(d1.EESP_AGE20to25) as EESP_AGE20to25
,sum(d1.EESP_AGE26to35) as EESP_AGE26to35
,sum(d1.EESP_AGE36to45) as EESP_AGE36to45
,sum(d1.EESP_AGE46to55) as EESP_AGE46to55
,sum(d1.EESP_AGE56to65) as EESP_AGE56to65
,sum(d1.EESP_AGE66to75) as EESP_AGE66to75
,sum(d1.EESP_AGEgt75) as EESP_AGEgt75
–how many children as follows
,sum(d1.CH_AGElt0) as CH_AGElt0
,sum(d1.CH_AGE0t24) as CH_AGE0t24
,sum(d1.CH_AGEgt25) as CH_AGEgt25
from ##meMemCnts1 d1 –dont forget to modify first program to put into actual table group by run_date
,end_date_requested –alliance does not change therefore it is not needed any different as built in the loop below
,Alliance
,AllianceNM

WHILE @loopvar1 < 5
BEGIN
DECLARE @sqlSELECT NVARCHAR(2000)
SET @sqlSELECT = case
when @loopvar1 = 1
then @sqlStmt1
else N’ UNION ‘+ @sqlSTMT1
end
DECLARE @FLD_comment NVARCHAR(4000)
SET @FLD_comment = case
when @loopvar1 = 1
then N’
,”1-ALLIANCE TOTALS” as Comment1 ‘ –keeping this like this helps to make the sql neater to look at for fixing/debug
when @loopvar1 = 2 –I guess it uses actual spacing in it’s interpretation
then N’
,”2-MKTSEG TOTALS” as Comment1 ‘
when @loopvar1 = 3
then N’
,”3-GROUP TOTALS” as Comment1 ‘
when @loopvar1 = 4
then N’
,”4-BRANCH TOTALS” as Comment1 ‘
else ‘ ‘ –null
end –alliance does not change so didn’t bother putting in loop
–starts mktseg here
DECLARE @FLD_mktseg NVARCHAR(4000)
SET @FLD_mktseg = case
when @loopvar1 = 1
then N’
,cast(” ” as varchar(20)) as MktSeg’
when @loopvar1 between 2 and 4
then N’
,cast(d1.MktSeg as varchar(20)) as MktSeg’
else ‘ ‘
end
DECLARE @FLD_mktsegNM NVARCHAR(4000)
SET @FLD_mktsegNM = case
when @loopvar1 = 1
then N’
,” ” as MktSegNM’
when @loopvar1 between 2 and 4
then N’
,d1.MktSegNM as MktSegNM’
else ‘ ‘
end
–groupid here
DECLARE @FLD_group NVARCHAR(4000)
SET @FLD_group = case
when @loopvar1 <= 2
then N’
,cast(” ” as varchar(20)) as GrpID’
when @loopvar1 between 3 and 4
then N’
,cast(d1.grpID as varchar(20)) as GrpID’
else ‘ ‘
end DECLARE @FLD_groupNM NVARCHAR(4000)
SET @FLD_groupNM = case
when @loopvar1 <= 2
then N’
,” ” as GrpNM’
when @loopvar1 between 3 and 4
then N’
,d1.grpNM as GrpNM’
else ‘ ‘
end –branch
DECLARE @FLD_branch NVARCHAR(4000)
SET @FLD_branch = case
when @loopvar1 <= 3
then N’
,cast(” ” as varchar(20)) as BRANCH’
when @loopvar1 = 4
then N’
,cast(d1.branch as varchar(20)) as BRANCH’
else ‘ ‘
end DECLARE @FLD_branchNM NVARCHAR(4000)
SET @FLD_branchNM = case
when @loopvar1 <= 3
then N’
,” ” as BranchNM’
when @loopvar1 = 4
then N’
,d1.branchNM ‘
else ‘ ‘
end –GROUP BYs START HERE
–Alliance is always avail so here is MARKET
DECLARE @GROUPBY_mkt NVARCHAR(4000)
SET @GROUPBY_MKT = case
when @loopvar1 >= 2 –between 2 and 3
then N’
,d1.MktSeg’
else ‘ ‘
end
DECLARE @GROUPBY_mktNM NVARCHAR(4000)
SET @GROUPBY_MKTNM = case
when @loopvar1 >= 2 –between 2 and 3
then N’
,d1.MktSegNM’
else ‘ ‘
end
— GROUP
DECLARE @GROUPBY_group NVARCHAR(4000)
SET @GROUPBY_GROUP = case
when @loopvar1 >= 3 –between 3 and 4
then N’
,d1.GrpID’
else ‘ ‘
end
DECLARE @GROUPBY_groupNM NVARCHAR(4000)
SET @GROUPBY_GROUPNM = case
when @loopvar1 >= 3 –between 3 and 4
then N’
,d1.GrpNM’
else ‘ ‘
end — BRANCH
DECLARE @GROUPBY_branch NVARCHAR(4000)
SET @GROUPBY_branch = case
when @loopvar1 = 4
then N’
,d1.branch’
else ‘ ‘
end DECLARE @GROUPBY_branchNM NVARCHAR(4000)
— union aLL –trying it here also resulted in the same error why?.
SET @GROUPBY_branchNM = case
when @loopvar1 = 4
then N’
,d1.branchNM
ORDER BY run_date
,end_date_requested
,comment1
,alliance
,mktseg
,grpID
,branch

else ‘ ‘
end DECLARE @UNIONstmt NVARCHAR(4000)
SET @UNIONstmt = case
when @loopvar1 < 4
then N’
UNION ALL’
ELSE ‘ ‘
END
–now put it all together into another var
declare @sqlRunIt nvarchar(4000)
set @sqlRunIt =
–@sqlSELECT –this has union preceding select –> notes work either on "exec"
@sqlstmt1 –initial attempt which I swear worked last thurs when citrix was having issues at work….
+ @fld_comment
+ @fld_alliance
+ @fld_alliancenm
+ @fld_mktseg
+ @fld_mktsegNM
+ @fld_group
+ @fld_groupnm
+ @fld_branch
+ @fld_branchnm
+ @sqlstmt2
+ @groupby_mkt
+ @GROUPBY_MKTNM
+ @groupby_group
+ @groupby_groupNM
+ @groupby_branch
+ @groupby_branchNM
+ @UNIONstmt –if I comment, it works fine but as 4 queries. Keeping seems to process all but only displays branch with those errs print @sqlRunIt
–exec sp_executesql @sqlRunIt
set @loopvar1 = @loopvar1 + 1
end –program and loop end here. –final step, insert the output above into a table
/*
–here are my errors when EXEC runs with @unionstmt active:
Server: Msg 170, Level 15, State 1, Line 61
Line 61: Incorrect syntax near ‘ALL’.
Server: Msg 170, Level 15, State 1, Line 63
Line 63: Incorrect syntax near ‘ALL’.
Server: Msg 170, Level 15, State 1, Line 65
Line 65: Incorrect syntax near ‘ALL’. (221 row(s) affected)
*/ –here is the code after PRINT runs, which, if I run this, it runs fine
/*
SELECT run_date
,end_date_requested ,’1-ALLIANCE TOTALS’ as Comment1
,cast(Alliance as varchar(20)) as Alliance
,AllianceNM
,cast(‘ ‘ as varchar(20)) as MktSeg
,’ ‘ as MktSegNM
,cast(‘ ‘ as varchar(20)) as GrpID
,’ ‘ as GrpNM
,cast(‘ ‘ as varchar(20)) as BRANCH
,’ ‘ as BranchNM
–memcntshere
,sum(d1.EEcnt) as EEcnt
,sum(d1.SPcnt) as SPcnt
,sum(d1.CHcnt) as CHcnt
,sum(d1.DEPScnt) as DEPScnt
,sum(d1.TOTcnt) as TOTcnt –webreghere
,sum(d1.EEcnt_webReg) as EEcnt_WebReg
,sum(d1.SPcnt_webReg) as SPcnt_WebReg
,sum(d1.CHcnt_webReg) as CHcnt_WebReg
,sum(d1.DEPScnt_webReg) as DEPScnt_WebReg
,sum(d1.TOTcnt_webReg) as TOTcnt_WebReg –hrahere
,sum(d1.EEcnt_HRA) as EEcnt_HRA
,sum(d1.SPcnt_HRA) as SPcnt_HRA
,sum(d1.CHcnt_HRA) as CHcnt_HRA
,sum(d1.DEPScnt_HRA) as DEPScnt_HRA
,sum(d1.TOTcnt_HRA) as TOTcnt_HRA
–EE & SPs Age ranges
,sum(d1.EESP_AGElt20) as EESP_AGElt20
,sum(d1.EESP_AGE20to25) as EESP_AGE20to25
,sum(d1.EESP_AGE26to35) as EESP_AGE26to35
,sum(d1.EESP_AGE36to45) as EESP_AGE36to45
,sum(d1.EESP_AGE46to55) as EESP_AGE46to55
,sum(d1.EESP_AGE56to65) as EESP_AGE56to65
,sum(d1.EESP_AGE66to75) as EESP_AGE66to75
,sum(d1.EESP_AGEgt75) as EESP_AGEgt75
–how many children as follows
,sum(d1.CH_AGElt0) as CH_AGElt0
,sum(d1.CH_AGE0t24) as CH_AGE0t24
,sum(d1.CH_AGEgt25) as CH_AGEgt25
from ##meMemCnts1 d1 –dont forget to modify first program to put into actual table group by run_date
,end_date_requested –alliance does not change therefore it is not needed any different as built in the loop below
,Alliance
,AllianceNM UNION ALL
SELECT run_date
,end_date_requested ,’2-MKTSEG TOTALS’ as Comment1
,cast(Alliance as varchar(20)) as Alliance
,AllianceNM
,cast(d1.MktSeg as varchar(20)) as MktSeg
,d1.MktSegNM as MktSegNM
,cast(‘ ‘ as varchar(20)) as GrpID
,’ ‘ as GrpNM
,cast(‘ ‘ as varchar(20)) as BRANCH
,’ ‘ as BranchNM
–memcntshere
,sum(d1.EEcnt) as EEcnt
,sum(d1.SPcnt) as SPcnt
,sum(d1.CHcnt) as CHcnt
,sum(d1.DEPScnt) as DEPScnt
,sum(d1.TOTcnt) as TOTcnt –webreghere
,sum(d1.EEcnt_webReg) as EEcnt_WebReg
,sum(d1.SPcnt_webReg) as SPcnt_WebReg
,sum(d1.CHcnt_webReg) as CHcnt_WebReg
,sum(d1.DEPScnt_webReg) as DEPScnt_WebReg
,sum(d1.TOTcnt_webReg) as TOTcnt_WebReg –hrahere
,sum(d1.EEcnt_HRA) as EEcnt_HRA
,sum(d1.SPcnt_HRA) as SPcnt_HRA
,sum(d1.CHcnt_HRA) as CHcnt_HRA
,sum(d1.DEPScnt_HRA) as DEPScnt_HRA
,sum(d1.TOTcnt_HRA) as TOTcnt_HRA
–EE & SPs Age ranges
,sum(d1.EESP_AGElt20) as EESP_AGElt20
,sum(d1.EESP_AGE20to25) as EESP_AGE20to25
,sum(d1.EESP_AGE26to35) as EESP_AGE26to35
,sum(d1.EESP_AGE36to45) as EESP_AGE36to45
,sum(d1.EESP_AGE46to55) as EESP_AGE46to55
,sum(d1.EESP_AGE56to65) as EESP_AGE56to65
,sum(d1.EESP_AGE66to75) as EESP_AGE66to75
,sum(d1.EESP_AGEgt75) as EESP_AGEgt75
–how many children as follows
,sum(d1.CH_AGElt0) as CH_AGElt0
,sum(d1.CH_AGE0t24) as CH_AGE0t24
,sum(d1.CH_AGEgt25) as CH_AGEgt25
from ##meMemCnts1 d1 –dont forget to modify first program to put into actual table group by run_date
,end_date_requested –alliance does not change therefore it is not needed any different as built in the loop below
,Alliance
,AllianceNM ,d1.MktSeg
,d1.MktSegNM
UNION ALL
SELECT run_date
,end_date_requested ,’3-GROUP TOTALS’ as Comment1
,cast(Alliance as varchar(20)) as Alliance
,AllianceNM
,cast(d1.MktSeg as varchar(20)) as MktSeg
,d1.MktSegNM as MktSegNM
,cast(d1.grpID as varchar(20)) as GrpID
,d1.grpNM as GrpNM
,cast(‘ ‘ as varchar(20)) as BRANCH
,’ ‘ as BranchNM
–memcntshere
,sum(d1.EEcnt) as EEcnt
,sum(d1.SPcnt) as SPcnt
,sum(d1.CHcnt) as CHcnt
,sum(d1.DEPScnt) as DEPScnt
,sum(d1.TOTcnt) as TOTcnt –webreghere
,sum(d1.EEcnt_webReg) as EEcnt_WebReg
,sum(d1.SPcnt_webReg) as SPcnt_WebReg
,sum(d1.CHcnt_webReg) as CHcnt_WebReg
,sum(d1.DEPScnt_webReg) as DEPScnt_WebReg
,sum(d1.TOTcnt_webReg) as TOTcnt_WebReg –hrahere
,sum(d1.EEcnt_HRA) as EEcnt_HRA
,sum(d1.SPcnt_HRA) as SPcnt_HRA
,sum(d1.CHcnt_HRA) as CHcnt_HRA
,sum(d1.DEPScnt_HRA) as DEPScnt_HRA
,sum(d1.TOTcnt_HRA) as TOTcnt_HRA
–EE & SPs Age ranges
,sum(d1.EESP_AGElt20) as EESP_AGElt20
,sum(d1.EESP_AGE20to25) as EESP_AGE20to25
,sum(d1.EESP_AGE26to35) as EESP_AGE26to35
,sum(d1.EESP_AGE36to45) as EESP_AGE36to45
,sum(d1.EESP_AGE46to55) as EESP_AGE46to55
,sum(d1.EESP_AGE56to65) as EESP_AGE56to65
,sum(d1.EESP_AGE66to75) as EESP_AGE66to75
,sum(d1.EESP_AGEgt75) as EESP_AGEgt75
–how many children as follows
,sum(d1.CH_AGElt0) as CH_AGElt0
,sum(d1.CH_AGE0t24) as CH_AGE0t24
,sum(d1.CH_AGEgt25) as CH_AGEgt25
from ##meMemCnts1 d1 –dont forget to modify first program to put into actual table group by run_date
,end_date_requested –alliance does not change therefore it is not needed any different as built in the loop below
,Alliance
,AllianceNM ,d1.MktSeg
,d1.MktSegNM
,d1.GrpID
,d1.GrpNM
UNION ALL
SELECT run_date
,end_date_requested ,’4-BRANCH TOTALS’ as Comment1
,cast(Alliance as varchar(20)) as Alliance
,AllianceNM
,cast(d1.MktSeg as varchar(20)) as MktSeg
,d1.MktSegNM as MktSegNM
,cast(d1.grpID as varchar(20)) as GrpID
,d1.grpNM as GrpNM
,cast(d1.branch as varchar(20)) as BRANCH
,d1.branchNM
–memcntshere
,sum(d1.EEcnt) as EEcnt
,sum(d1.SPcnt) as SPcnt
,sum(d1.CHcnt) as CHcnt
,sum(d1.DEPScnt) as DEPScnt
,sum(d1.TOTcnt) as TOTcnt –webreghere
,sum(d1.EEcnt_webReg) as EEcnt_WebReg
,sum(d1.SPcnt_webReg) as SPcnt_WebReg
,sum(d1.CHcnt_webReg) as CHcnt_WebReg
,sum(d1.DEPScnt_webReg) as DEPScnt_WebReg
,sum(d1.TOTcnt_webReg) as TOTcnt_WebReg –hrahere
,sum(d1.EEcnt_HRA) as EEcnt_HRA
,sum(d1.SPcnt_HRA) as SPcnt_HRA
,sum(d1.CHcnt_HRA) as CHcnt_HRA
,sum(d1.DEPScnt_HRA) as DEPScnt_HRA
,sum(d1.TOTcnt_HRA) as TOTcnt_HRA
–EE & SPs Age ranges
,sum(d1.EESP_AGElt20) as EESP_AGElt20
,sum(d1.EESP_AGE20to25) as EESP_AGE20to25
,sum(d1.EESP_AGE26to35) as EESP_AGE26to35
,sum(d1.EESP_AGE36to45) as EESP_AGE36to45
,sum(d1.EESP_AGE46to55) as EESP_AGE46to55
,sum(d1.EESP_AGE56to65) as EESP_AGE56to65
,sum(d1.EESP_AGE66to75) as EESP_AGE66to75
,sum(d1.EESP_AGEgt75) as EESP_AGEgt75
–how many children as follows
,sum(d1.CH_AGElt0) as CH_AGElt0
,sum(d1.CH_AGE0t24) as CH_AGE0t24
,sum(d1.CH_AGEgt25) as CH_AGEgt25
from ##meMemCnts1 d1 –dont forget to modify first program to put into actual table group by run_date
,end_date_requested –alliance does not change therefore it is not needed any different as built in the loop below
,Alliance
,AllianceNM ,d1.MktSeg
,d1.MktSegNM
,d1.GrpID
,d1.GrpNM
,d1.branch
,d1.branchNM
ORDER BY run_date
,end_date_requested
,comment1
,alliance
,mktseg
,grpID
,branch */

Not sure I understand your question – there’s too much detail, and not enough outline. Anyways …
If I understand correctly, you’re building dynamic SQL – either this way:
SELECT blabla1
UNION
SELECT blabla2
— which will return a single combined resultset from the two SELECT statements —
or this way:
SELECT blabla1
SELECT blabla2
— which will return a single resultset from the second SELECT statement only —

Sorry for the detail, I didn’t know what to post since I really don’t know where my error is coming from and really do not know how to explain any simpler.
To answer your question, your first response seems to be what I need; the ability to build a dynamic SQL stmt:
SELECT blabla1
UNION
SELECT blabla2
— which will return a single combined resultset from the two SELECT statements —

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |