通过XSL制作报表可以将数据和显示相分离,可以制作多节点报表
例如
select
table1.SortDate
,table2.LeaveBeginDate
,table2.LeaveEndDate
,table2.PerID
,table2.Name
from #temptable table1,#temptable table2
where table1.CodeID=table2.CodeID
for xml auto
可以制作两个节点的报表,
select
table1.SortDate
,table2.LeaveBeginDate
,table2.LeaveEndDate
,table2.PerID
,table2.Name
,table3.Name
可以制作三个节点的报表,
对于系统中通过多选ID,然后执行批量操作的时候,有两种方法来处理语句,
1.通过拼接SQL语句实现,例如传入的ID为@ID
set @SQLList ='select ID into ##temp10 from Person_Main where ID in ('''+replace(@ID,',',''',''')+''')'
通过这种方式,将ID插入到一个表中,注意,在拼接的SQL语句中,如果要生成临时表,只能用全局临时表,以##开头
--print @SQLList
exec (@SQLList)
select
table1.ID as CodeID
,convert(varchar(10),table1.LeaveBeginDate,120) as SortDate
,convert(varchar(20),table1.LeaveBeginDate,120) as LeaveBeginDate
,convert(varchar(20),table1.LeaveEndDate,120) as LeaveEndDate
,table2.ID as PerID,table2.Name
into #temptable
from SalesGeneralSituation table1
,person_main table2
,##temp10
where table1.PerID = ##temp10.id
通过这条语句对ID进行选择
and table1.PerID=table2.id
and convert(varchar(10),table1.RegisterDate,120) between @BeginDate and @EndDate order by table1.LeaveBeginDate,table2.Tnum
2.通过拼接SQL的方式,例如
set @execString='update seat set disableflag=''1101'' where ID in ('''+replace(@ID,',',''',''')+''') and seattype='''' '
exec(@execString)