1:  存储过程
2:   
3:   
4:  if exists(select name from sysobjects where name='proc_HisChange' and type='p')
5:  drop proc proc_ToHisChange
6:   
7:  go
8:  create proc proc_ToHisChange
9:  @oldHisID varchar(64),            --原来的病历号
10:  @PatientName varchar(50),        --英文姓名
11:  @ChineseName varchar(50),        --中文名字
12:  @newHisID varchar(64),            --新病历号
13:  @result int output                --输出执行结果
14:   
15:  as
16:  begin
17:
18:  declare
19:  @count_temp int,
20:  @sql varchar(2000),
21:  @sql_study varchar(2000),
22:  @log varchar(2000),
23:  @logid int,
24:  @examineid int
25:   
26:  set @count_temp=0
27:  set @sql='update patient set '
28:  set @sql_study='update study set '
29:  set @log='修改信息:'
30:  set @logid=0
31:   
32:  begin transaction
33:  begin try
34:  if @oldHisID <>''
35:  begin
36:      select @count_temp=count(*) from patient where HisID=@oldHisID
37:      if @count_temp>0
38:      begin
39:          if @newHisID<>''
40:          begin
41:              set @sql=@sql+' HisID='''+@newHisID+''''+','
42:              select @log=@log +'病历号:('+HisID+')->('+@newHisID+');' from    patient where hisid=@oldHisID
43:              set @sql_study=@sql_study+' patientid='''+@newHisID+''''+','
44:              select @log=@log +'study.病历号:('+patientid+')->('+@newHisID+');' from    study where patientid=@oldHisID
45:          end
46:          if @patientname<>''
47:          begin
48:               set @sql=@sql + ' patientname='''+@patientname+''''+','
49:               select @log=@log +'姓名:('+patientname+')->('+@patientname+');' from    patient where hisid=@oldHisID
50:               --set @log=@log+')->('+@patientname+');'
51:          end
52:          if @ChineseName<>''
53:          begin
54:              set @sql=@sql+' ChineseName='''+@ChineseName+''''+','
55:              select @log=@log +'中文姓名:('+ChineseName +')->('+@ChineseName+');' from    patient where hisid=@oldHisID
56:              --set @log=@log+')->('+@ChineseName+');'
57:          end
58:   
59:
60:          --去掉结尾的,号
61:          set @sql=substring(@sql,1,len(@sql)-1)
62:          set @sql_study=substring(@sql_study,1,len(@sql_study)-1)
63:          --拼接查询条件
64:          set @sql=@sql+' where HisID='''+@oldHisID+''''
65:          set @sql_study=@sql_study+' where patientid='''+@oldHisID+''''
66:          --print 'ok7'
67:          --print @sql_study
68:          --print @log
69:          exec(@sql)
70:          exec(@sql_study)
71:          ---日志文件
72:          select @logid=max(logid)+1,@examineid=max(examineid)+1 from eventlog
73:          insert into eventlog(logid,examineid,sourcetype,eventtime,eventtype,description,logtype,officeid)
74:                      values(@logid,@examineid,-1,getdate(),'保留',@log,-1,-1)
75:          set @result=1
76:      end
77:  end
78:      commit transaction
79:  end try
80:  begin catch
81:      set @result=0
82:      rollback transaction
83:  end catch
84:  end
85:  go

游标

1:  --创建GetReportTime
2:  IF EXISTS(SELECT * FROM sysobjects WHERE id = object_id('GetReportTime'))
3:      DROP FUNCTION GetReportTime
4:  GO
5:  CREATE FUNCTION GetReportTime(@ReportsStatus INT, @StudiesIndex INT) RETURNS VARCHAR(23)
6:  AS
7:  BEGIN
8:      DECLARE @ReportTime VARCHAR(23)
9:      IF @ReportsStatus = 120  --获取修订时间
10:  SET @ReportTime = (SELECT TOP 1 reviewsdate + reviewstime AS reviewstime FROM EISReports
11:  WHERE ReportsStudies = @StudiesIndex AND reportsstatus = @ReportsStatus ORDER BY reviewstime DESC)
12:      ELSE
13:      BEGIN
14:  IF @ReportsStatus = 100 --获取审核时间
15:      SET @ReportTime = (SELECT TOP 1 approvedate + approvetime AS audittime FROM EISReports
16:      WHERE ReportsStudies = @StudiesIndex AND reportsstatus = @ReportsStatus ORDER BY approvetime DESC)
17:  ELSE
18:  BEGIN
19:      IF (@ReportsStatus = 1) OR (@ReportsStatus = 10) --获取书写时间
20:  SET @ReportTime = (SELECT TOP 1 reportsdate + reportstime AS writetime FROM EISReports
21:  WHERE ReportsStudies = @StudiesIndex ORDER BY reportstime DESC)
22:      ELSE
23:  SET @ReportTime = ' '
24:       END
25:      END
26:      RETURN @ReportTime
27:  END
28:  GO
29:   

函数

1: DECLARE @ExamineID INT
2: DECLARE @ExaminePart VARCHAR(100)
3: 
4: DECLARE cstmpExaminePartStat Cursor FOR
5: SELECT ExamineID, ExaminePart FROM tmpExaminePartStat
6: 
7: OPEN cstmpExaminePartStat
8: FETCH NEXT FROM cstmpExaminePartStat INTO @ExamineID, @ExaminePart
9: WHILE @@fetch_status = 0
10: BEGIN
11: IF EXISTS(SELECT TOP 1 ExamineSort FROM ExamPartSort WHERE ExamPart = @ExaminePart)
12:   UPDATE tmpExaminePartStat SET ExaminePart = (SELECT TOP 1 ExamineSort FROM ExamPartSort
13:     WHERE ExaminePart = @ExaminePart) WHERE ExamineID = @ExamineID AND ExaminePart = @ExaminePart
14: 
15: FETCH NEXT FROM cstmpExaminePartStat INTO @ExamineID, @ExaminePart
16: END
17: 
18: CLOSE cstmpExaminePartStat
19: DEALLOCATE cstmpExaminePartStat
20: 
21: