/********* tables *************/ CREATE TABLE xsequences ( id int IDENTITY(1,1) NOT NULL, code varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, prefix varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT '' NOT NULL, digits int DEFAULT 1 NOT NULL, currsec int DEFAULT 0 NOT NULL, PRIMARY KEY (id) ); CREATE INDEX ndx_seq_code_prf ON xsequences(code, prefix); CREATE TABLE HISDBTST.dbo.xsequences_format ( id int IDENTITY(1,1) NOT NULL, code varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, prefix varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT '' NOT NULL, digits int DEFAULT 1 NOT NULL, PRIMARY KEY (id) ); CREATE INDEX ndx_seq_fmt_code ON xsequences_format(code); /************************ p_sequence: Sequence generator ***********/ /* code prefix digits (zero filled) -------------- ----------- ------ PATRECORD yyMM- 4 EMERGENCY "EM"yyMMdd- 3 OUTPATIENT "OP"yyMMdd- 3 INPATIENT "IP"yyMMdd- 3 REQUIREMENT "RQ"yyMMdd- 3 */ CREATE procedure [dbo].p_sequence( @code varchar(20), @date datetime, @result varchar(100) output ) as begin declare @currsec int; declare @prefix varchar(20); declare @digits int; select @prefix=prefix, @digits=digits from xsequences_format sf where code=@code; set @prefix = format(isnull(@date,getdate()), @prefix); insert into xsequences(code, prefix, currsec) select @code, @prefix, 0 WHERE NOT EXISTS(SELECT ID FROM xsequences WHERE code=@code and prefix=@prefix); update dbo.xsequences set currsec=currsec + 1, @currsec = currsec + 1 WHERE code=@code and prefix=@prefix; set @result = isnull(@prefix,'') + dbo.lpad(@currsec, @digits); end; /********************* trigger example **************************/ CREATE TRIGGER [dbo].t_patient_record ON [dbo].patient for insert not for replication AS BEGIN SET NOCOUNT ON; declare @RecordNo varchar(30) declare @Date datetime = GETDATE(); -- select @Date = Creation from inserted; /***/ exec p_sequence 'RECORD', @Date, @RecordNo output; /***/ update Pacient set RecordNo=@RecordNo where PatientId =(select PatientId from inserted) END
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter