问题描述
在MS-SQL Server中,是否可以将参数传递给 DEFAULT 函数,或者以某种方式将 DEFAULT 插入到记录中的值的值?
您可以为MS-SQL Server中的列分配函数作为DEFAULT值。使用这个,我试图达到以下目的。对于有研究ID和患者ID的表格,我想在该研究中自动分配一个新的患者编号。
CREATE TABLE [ dbo]。[PATIENT_STUDY](
[PatientStudyID] [int] IDENTITY(1,1)NOT NULL,
[PatientID] [int] NOT NULL,
[StudyID] [int] NOT NULL,
[PatientCode] [varchar(30)] NULL,
[ActiveParticipant] [tinyint] NULL - 1 =是
)
- 设置函数作为列的默认值PatientCode
ALTER TABLE PATIENT_STUDY
ADD CONSTRAINT
DF_PATIENT_STUDY_CODE
DEFAULT([dbo]。[fn_Generate_PatientCode]())
用于PatientCode
例如,当病人被添加到STID = 67时(例如学习代码012),并且该研究的最后一个患者代码是012-00024,那么下一个患者代码应该是012-00025。
ID PatientID StudyID PatientCode Active
--- --------- ------- ----------- ------ -
101 92 65'009-00031'1
102 93 66'010-00018'1
103 94 67'012-00023'1
104 95 67'012-00024'1
我知道这可以通过 INSERT 触发器来实现,但我想知道是否也可以使用DEFAULT,因为这是一个更清洁的解决方案,更容易维护。
我试过以下。
CREATE FUNCTION [dbo]。[fn_Generate_PatientCode]()
RETURNS VARCHAR(10) - 例如012-00345
AS
BEGIN
- 变量
DECLARE @Code_next INT
DECLARE @Code_new VARCHAR(10)
- 查找当前记录的值, ?
DECLARE @STID INT
SET @STID = ?? - 如何确定当前记录? < --------------------- !!
- 确定学习代码,例如'023-00456' - > '023'
SET @StudyCode =(SELECT StudyCode FROM Study WHERE StudyID = @STID)
- 确定每个研究的最大研究nr,例如'123-00456' - > 456
SET @Code_next =(SELECT MAX(SUBSTRING(PatientCode,5,5))
FROM PATIENT_STUDY
WHERE IsNumeric(SUBSTRING(PatientCode,5,5))= 1
AND StudyID = @STID
)+ 1 - 获得下一个数字
- 检查本研究中的第一位患者是否为
IF(@Code_next为空)
BEGIN
SET @Code_next = 1
END
- 前缀必须为零
SET @Code_new = @Code_next
WHILE(LEN(@Code_new) < 5)SET @Code_new ='0'+ @Code_new
- 构建新的患者代码,示例'012-00025'
SET @Code_new = @StudyCode +' - '+ @Code_new
- 返回值
RETURN @Code_new
END
In MS-SQL Server, is it possible to pass a parameter to a DEFAULT function, or somehow base the DEFAULT value on the values inserted in the record?
You can assign a function as a DEFAULT value for a column in MS-SQL Server. Using this, I'm trying to achieve the following. For a table with study ids and patient ids, I want to automatically assign a new patient number within that study.
CREATE TABLE [dbo].[PATIENT_STUDY]( [PatientStudyID] [int] IDENTITY(1,1) NOT NULL, [PatientID] [int] NOT NULL, [StudyID] [int] NOT NULL, [PatientCode] [varchar(30)] NULL, [ActiveParticipant] [tinyint] NULL -- 1=yes ) -- set function as DEFAULT for column PatientCode ALTER TABLE PATIENT_STUDY ADD CONSTRAINT DF_PATIENT_STUDY_CODE DEFAULT([dbo].[fn_Generate_PatientCode]()) FOR PatientCode GO
So for example when a patient is added to STID=67 (for example with studycode "012"), and the last patientcode for that study was "012-00024", then the next patientcode should be "012-00025".
ID PatientID StudyID PatientCode Active --- --------- ------- ----------- ------- 101 92 65 '009-00031' 1 102 93 66 '010-00018' 1 103 94 67 '012-00023' 1 104 95 67 '012-00024' 1
I know this can be achieved with a INSERT trigger, but I was wondering if it's also possible with DEFAULT, as this is a somewhat cleaner solution, easier to maintain.
I've tried the following.
CREATE FUNCTION [dbo].[fn_Generate_PatientCode]() RETURNS VARCHAR(10) -- for example "012-00345" AS BEGIN -- variables DECLARE @Code_next INT DECLARE @Code_new VARCHAR(10) -- find values of current record, how? DECLARE @STID INT SET @STID = ?? -- How to determine current record? <--------------------- !! -- determine the studycode, example '023-00456' -> '023' SET @StudyCode = (SELECT StudyCode FROM Study WHERE StudyID = @STID) -- determine max study nr per study, example '123-00456' -> 456 SET @Code_next = (SELECT MAX(SUBSTRING(PatientCode, 5, 5)) FROM PATIENT_STUDY WHERE IsNumeric(SUBSTRING(PatientCode, 5, 5)) = 1 AND StudyID = @STID ) + 1 -- get next number -- check if first patient in this study IF (@Code_next is null) BEGIN SET @Code_next = 1 END -- prefix with zeroes if needed SET @Code_new = @Code_next WHILE (LEN(@Code_new) < 5) SET @Code_new = '0' + @Code_new -- build new patient code, example '012-00025' SET @Code_new = @StudyCode + '-' + @Code_new -- return value RETURN @Code_new END
No, I don't think you can do it without a trigger.
这篇关于具有DEFAULT功能的列,传递参数或确定插入值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!