使用SQL PROCEDURE SPLIT方法將大量資料','讀取後INSERT
1.姓名增加SPLIT做第一個迴圈
2.日期做第二個迴圈判斷是否有相對資料,並寫入暫存檔
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[#rptMMS_ALARM_TEMP]'))
DROP TABLE [dbo].#rptMMS_ALARM_TEMP --報表暫存資料表
CREATE TABLE [dbo].#rptMMS_ALARM_TEMP (
pro_name nchar(50), --程式代碼
num nchar(15), --病歷號
name nchar(50), --姓名
in_date nchar(10), --日期起
out_date nchar(10)) --日期訖
--人員SPLIT(先裁人員出來)CURSOR--
DECLARE @CIndex smallint
WHILE (@num<>'')
BEGIN
SET @CIndex = CHARINDEX(',',@num) --SQL SPLIT
IF @CIndex = 0 SET @CIndex = LEN(@num) + 1
SET @split_num = SUBSTRING(@num,1,@CIndex-1)--取第一個
--print @num
--END人員CURSOR--
--日期-CURSOR
DECLARE alarm_cursor CURSOR FOR
select in_datetime,out_datetime from rptDateTime_report ORDER BY in_datetime
OPEN alarm_cursor
FETCH NEXT FROM alarm_cursor
INTO @in_alarm_date,@out_alarm_date
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT TOP 1 @num_name = [mpd_name] FROM [MMSDB].[dbo].[mms_patient_data]
WHERE mpd_num = @split_num ORDER BY mpd_in_date DESC
--特殊心理治療
SELECT @count_alarm = count(*) FROM [MMSDB].[dbo].[mms_specphy_treat]
WHERE mst_write_date between @in_alarm_date and @out_alarm_date
AND mst_num = @split_num
--如果筆數<=0 代表沒寫過,才警示
IF @count_alarm <=0
BEGIN
INSERT INTO #rptMMS_ALARM_TEMP
VALUES ('特殊心理治療',@split_num,@num_name,@in_alarm_date,@out_alarm_date)
END
SET @count_alarm = 0
--END
--生理心理功能檢查（14天一次）--pat_phy_inspection
--14天(1)取餘數<>0(單數)和IN_DATE為空,則寫入IN_DATE=起時間
SELECT @count = count(*) FROM [MMSDB].[dbo].rptDateTime_report
SELECT @mod_count = @mod_num_count % 2
IF @mod_count <> 0 AND @in_date_1 = '' --單數
BEGIN
SET @in_date_1 = @in_alarm_date
END
--取餘數=0(雙數)和IN_DATE<> ''
IF @mod_count = 0 AND @in_date_1 <> ''
BEGIN
--開始以IN_DATE與@OUT_ALARM_DATE抓取COUNT數
SELECT @count_alarm = count(*) FROM [MMSDB].[dbo].pat_phy_inspection
WHERE ppi_write_date between @in_date_1 and @out_alarm_date
AND ppi_num = @split_num
--當COUNT<=0則寫入TEMP_TABLE內
IF @count_alarm <=0
BEGIN
INSERT INTO #rptMMS_ALARM_TEMP
VALUES ('生理心理功能檢查',@split_num,@num_name,@in_date_1,@out_alarm_date)
--清空IN_DATE
SET @in_date_1 = ''
END
ELSE --雖條件符合,但有筆數,沒INSERT也要清空IN_DATE
BEGIN
SET @in_date_1 = ''
END
END
IF @count = @mod_num_count AND @mod_count <> 0
BEGIN
INSERT INTO #rptMMS_ALARM_TEMP
VALUES ('生理心理功能檢查',@split_num,@num_name,@in_date_1,@out_alarm_date)
SET @in_date_1 = ''
END
SET @mod_num_count = @mod_num_count + 1
--END 生理心理功能檢查
FETCH NEXT FROM alarm_cursor
INTO @in_alarm_date,@out_alarm_date
END
CLOSE alarm_cursor
DEALLOCATE alarm_cursor
----日期-CURSOR-END
IF @CIndex = LEN(@num) + 1 BREAK --算出目前num總長度
SET @num = SUBSTRING(@num,@CIndex+1,LEN(@num)) --刪除已跑過num再抓取
END
--end 人員
--------------------------------------------------------------------------------------------------
同場加印:
VB.NET要用DATATABLE去接回TEMP_TABLE的資料
Dim dtWrite_data As DataTable = DBUtil.GetDataTable(CommandType.StoredProcedure, "MMS_AlarmPatientData", arParames)
If dtWrite_data.Rows.Count > 0 Then
For Each drWrite_data As DataRow In dtWrite_data.Rows
pro_name = drWrite_data.Item("pro_name").ToString.Trim
num = drWrite_data.Item("num").ToString.Trim
name = drWrite_data.Item("name").ToString.Trim
total_date = drWrite_data.Item("in_date").ToString.Trim & "~" & drWrite_data.Item("out_date").ToString.Trim
dr = dt_temp.NewRow()
dr("bed_num") = pro_name.ToString.Trim
dr("num") = num.ToString.Trim
dr("name") = name.ToString.Trim
dr("write_date") = total_date.ToString.Trim
dt_temp.Rows.Add(dr)
Next
rptUtil.SetMyReportViewer(dt_temp)
PrintOut(frm_report, dt, rptUtil, rpt_mode, 1)
Else
MsgBox("該選取範圍無資料顯示 !", MsgBoxStyle.OkOnly + MsgBoxStyle.Information, "提示")
End If
--------------------------------------------------------------------------------------------------
參考文件