测试时，经常需要生成大量数据来测试系统性能，此功能可以用存储过程快速生成。
1. 随机生成日期
DECLARE @Date_start datetime
DECLARE @Date_end datetime
SET @Date_start= '1930-01-01'
SET @Date_end=getdate()
select @birthDate=dateadd(minute,abs(checksum(newid()))%(datediff(minute,@Date_start,@Date_end)+1),@Date_start)
2. 随机从给定的若干值中挑选一个(例如随机生成性别)
DECLARE @sex NVARCHAR(10)
SET @sex= CONVERT(NVARCHAR,cast( RAND()*3 as int))
IF @sex=''
SET @sex='Male';
ELSE IF
@sex=''
SET @sex='Female';
ELSE IF @sex=''
SET @sex=NULL;
3. 生成编号
DECLARE @subCode_base NVARCHAR(30)
DECLARE @barcode NVARCHAR(30)
SET @subCode_base='AutoSubCode_'
SET @subCode=@subCode_base+CONVERT(NVARCHAR,@index)
4. 单表插入存储过程
CREATE PROCEDURE [dbo].[add_SubjectInfo]
AS
DECLARE @subCode_base NVARCHAR(30)
DECLARE @barcode_base NVARCHAR(30)
DECLARE @birthDate datetime
DECLARE @sex NVARCHAR(10)
DECLARE @fullName_base NVARCHAR(30)
DECLARE @mortalityStatus NVARCHAR(30)
DECLARE @reserved NVARCHAR(10)
DECLARE @recordCreateDate datetime
DECLARE @recordCreator INTEGER DECLARE @count INTEGER
DECLARE @index INTEGER DECLARE @subCode NVARCHAR(30)
DECLARE @barcode NVARCHAR(30)
DECLARE @fullName NVARCHAR(30) DECLARE @Date_start datetime
DECLARE @Date_end datetime SET @subCode_base='AutoSubCode_'
SET @barcode_base='AutoBM_' SET @Date_start= '1930-01-01'
SET @Date_end=getdate() SET @fullName_base='AutoFullName_'
SET @recordCreateDate=GETDATE()
SET @recordCreator=22
-- 调整生成的条数=@count-@index
SET @count=10
SET @index=1 WHILE @index<@count
BEGIN
-- 生产编号
SET @subCode=@subCode_base+CONVERT(NVARCHAR,@index)
SET @barcode=@barcode_base+CONVERT(NVARCHAR,@index)
SET @fullName=@fullName_base+CONVERT(NVARCHAR,@index) -- 随机生成性别 Male/Female/空
SET @sex= CONVERT(NVARCHAR,cast( RAND()*3 as int))
IF @sex=''
SET @sex='Male';
ELSE IF
@sex=''
SET @sex='Female';
ELSE IF @sex=''
SET @sex=NULL; -- 随机生成存活状态 Dead/Alive/空
SET @mortalityStatus = CONVERT(NVARCHAR,cast( RAND()*3 as int))
IF @mortalityStatus=''
SET @mortalityStatus='Dead';
ELSE IF
@mortalityStatus=''
SET @mortalityStatus='Alive';
ELSE IF @mortalityStatus=''
SET @mortalityStatus=NULL; -- 随机生成Reserved状态 Yes/No/空
SET @reserved = CONVERT(NVARCHAR,cast( RAND()*3 as int))
IF @reserved=''
SET @reserved='Yes';
ELSE IF
@reserved=''
SET @reserved='No';
ELSE IF @reserved=''
SET @reserved=NULL;
-- 随机生产日期
select @birthDate=dateadd(minute,abs(checksum(newid()))%(datediff(minute,@Date_start,@Date_end)+1),@Date_start) INSERT INTO subject(subject_code,barcode, birth_date,sex, full_name,mortality_status,reserved, record_create_date,record_creator)
VALUES (@subCode, @barcode,@birthDate,@sex,@fullName,@mortalityStatus,@reserved,@recordCreateDate,@recordCreator) SET @index=@index+1
END
5 多表插入存储过程
USE [bio-d]
GO
/****** Object: StoredProcedure [dbo].[add_SubjectAndSubjectStudyInfo] Script Date: 2018/8/23 14:30:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[add_SubjectAndSubjectStudyInfo]
AS
-- 公用参数 DECLARE @subid_index INTEGER -- 获取Subject 表的最大id+1 作为添加的患者编号后缀起点
DECLARE @count INTEGER -- 用户作为循环的跳出条件
DECLARE @insertRow INTEGER -- 一次需要插入的条数 SET @insertRow=10000
-- 调整生成的条数=@count-@index
SET @subid_index=((select max(id) from subject)+1) --需要更换为动态的
SET @count=@subid_index+@InsertRow -- 插入Subject表
DECLARE @sub_code_base NVARCHAR(30)
DECLARE @sub_barcode_base NVARCHAR(30)
DECLARE @sub_birthdate DATETIME
DECLARE @sub_sex NVARCHAR(10)
DECLARE @sub_fullName_base NVARCHAR(30)
DECLARE @sub_mortalityStatus NVARCHAR(30)
DECLARE @sub_reserved NVARCHAR(10)
DECLARE @sub_recordCreateDate DATETIME
DECLARE @sub_recordCreator INTEGER DECLARE @sub_code NVARCHAR(30)
DECLARE @sub_barcode NVARCHAR(30)
DECLARE @sub_fullName NVARCHAR(30) DECLARE @Date_start DATETIME
DECLARE @Date_end DATETIME SET @sub_code_base='AutoSubCode_'
SET @sub_barcode_base='AutoBM_' SET @Date_start= '1930-01-01'
SET @Date_end=getdate() SET @sub_fullName_base='AutoFullName_'
SET @sub_recordCreateDate=GETDATE()
SET @sub_recordCreator=22 -- 插入Subject_study表
DECLARE @study_id INTEGER SET @study_id=10082 -- 插入Biomaterial表
DECLARE @bio_barcode_base NVARCHAR(30)
DECLARE @bioName_base NVARCHAR(30)
DECLARE @bio_recordCreateDate datetime
DECLARE @bio_recordCreator INTEGER DECLARE @bio_barcode NVARCHAR(30)
DECLARE @bio_bioName NVARCHAR(30) SET @bio_barcode_base='AutoBioBM_'
SET @bioName_base='AutoBioName_' -- 插入biomaterial_study表
DECLARE @biomaterial_id INTEGER
SET @biomaterial_id=(select max(id) from biomaterial)+1 WHILE @subid_index<@count
BEGIN
-- 生产编号
SET @sub_code=@sub_code_base+CONVERT(NVARCHAR,@subid_index)
SET @sub_barcode=@sub_barcode_base+CONVERT(NVARCHAR,@subid_index)
SET @sub_fullName=@sub_fullName_base+CONVERT(NVARCHAR,@subid_index) -- 随机生成性别 Male/Female/空
SET @sub_sex= CONVERT(NVARCHAR,cast( RAND()*3 as int))
IF @sub_sex=''
SET @sub_sex='Male';
ELSE IF
@sub_sex=''
SET @sub_sex='Female';
ELSE IF @sub_sex=''
SET @sub_sex=NULL; -- 随机生成存活状态 Dead/Alive/空
SET @sub_mortalityStatus = CONVERT(NVARCHAR,cast( RAND()*3 as int))
IF @sub_mortalityStatus=''
SET @sub_mortalityStatus='Dead';
ELSE IF
@sub_mortalityStatus=''
SET @sub_mortalityStatus='Alive';
ELSE IF @sub_mortalityStatus=''
SET @sub_mortalityStatus=NULL; -- 随机生成Reserved状态 Yes/No/空
SET @sub_reserved = CONVERT(NVARCHAR,cast( RAND()*3 as int))
IF @sub_reserved=''
SET @sub_reserved='Yes';
ELSE IF
@sub_reserved=''
SET @sub_reserved='No';
ELSE IF @sub_reserved=''
SET @sub_reserved=NULL;
-- 随机生产日期
select @sub_birthdate=dateadd(minute,abs(checksum(newid()))%(datediff(minute,@Date_start,@Date_end)+1),@Date_start) -- 插入Subject表
INSERT INTO subject(subject_code,barcode, birth_date, sex, full_name,mortality_status,reserved, record_create_date,record_creator)
VALUES (@sub_code, @sub_barcode,@sub_birthdate,@sub_sex,@sub_fullName,@sub_mortalityStatus,@sub_reserved,@sub_recordCreateDate,@sub_recordCreator) -- 插入Subject_study表
INSERT INTO subject_study(subject_id,study_id)
VALUES (@subid_index,@study_id) -- 插入Biomaterial表
-- 生产编号
SET @bio_barcode=@bio_barcode_base+CONVERT(NVARCHAR,@subid_index)
SET @bio_bioName=@bioName_base+CONVERT(NVARCHAR,@subid_index)
INSERT INTO biomaterial(at_facility,bar_code, batch_id, biomaterial_name,carrier,concentration,concentration_unit1,container_type,created_date, current_status, external_id,external_source, mass,mass_units,parent_id, storage_location,subject_id, tracking_number,volume,volume_units,notes,record_create_date, record_creator, concentration_unit2)
VALUES ( 2, @bio_barcode ,'', @bio_bioName, NULL, '', '', 1, NULL, 'In Inventory', '', '', '', '', -1 ,'', @subid_index,'', '', '', '', @bio_recordCreateDate,@bio_recordCreator,''); -- 插入biomaterial_study 表
INSERT INTO biomaterial_study(study_id,biomaterial_id)
VALUES(@study_id,@biomaterial_id) -- 插入样本和附件的关联
INSERT INTO attachment_associated(type,belong_id,attachment_id) values(2,@biomaterial_id,220)
INSERT INTO attachment_associated(type,belong_id,attachment_id) values(2,@biomaterial_id,221)
INSERT INTO attachment_associated(type,belong_id,attachment_id) values(2,@biomaterial_id,236)
INSERT INTO attachment_associated(type,belong_id,attachment_id) values(2,@biomaterial_id,237)
INSERT INTO attachment_associated(type,belong_id,attachment_id) values(2,@biomaterial_id,251)
INSERT INTO attachment_associated(type,belong_id,attachment_id) values(2,@biomaterial_id,252)
INSERT INTO attachment_associated(type,belong_id,attachment_id) values(2,@biomaterial_id,253)
SET @subid_index=@subid_index+1
END