我想将从sp_formCreateEventID
存储过程返回的值赋给一个新变量(@eventId
)。我最初认为this是最好的选择。此解决方案也与SSMS生成的EXEC命令一致。
然而,由于某些原因,EXEC
行如期从存储过程返回了一个INT
,但是当它不能将它的值赋给@eventId
变量时。
DECLARE @eventId INT
EXEC @eventId = sp_formCreateEventID @patientId, @programId, @clinicianId, @formId, @phaseTypeId, @draft, @dataCollectionDate, NULL
SELECT @eventId
sp_formCreateEventID
(别恨我,这不是我写的…):
ALTER PROCEDURE [dbo].[sp_formCreateEventID]
@PatientID int,
@ProgramID int,
@ClinicianID int,
@FormID int,
@PhaseTypeID int,
@Draft varchar(5),
@CompletedDate varchar(40),
@UserID int = null
AS
BEGIN
IF @CompletedDate = ''
SET @CompletedDate = NULL
--for some forms such as Clinical Input - Initial, there should only have one form filled for a youth. If that is the case and the event has existed, just return that one.
DECLARE @EID int
SET @EID = dbo.fn_GetExistingOnlyOneEventID(@PatientID, @ProgramID, @FormID)
PRINT @eid
IF @EID <> -99
BEGIN
SELECT
@EID AS 'EventID'
RETURN
END
DECLARE @TxCycleID int
DECLARE @TxCyclePhaseTypeID int
DECLARE @TxCyclePhaseID int
DECLARE @seqNum int
DECLARE @NewEventID INT
--if there is no cycle for this patient for this program, then create one.
IF NOT EXISTS (SELECT * FROM TxCycle WHERE PatientID = @PatientID AND ProgID = @ProgramID)
BEGIN
INSERT INTO TxCycle
(OpenDate, PatientID, ProgID)
VALUES
(GETDate(), @PatientID, @ProgramID)
END
SELECT
@TxCycleID = Max(TxCycleID)
FROM TxCycle
WHERE
PatientID = @PatientID AND
ProgID = @ProgramID
--In this cycle, for the current phase type, get the max seq number
IF EXISTS (SELECT * FROM TxCyclePhase WHERE TxCycle = @TxCycleID)
BEGIN
SELECT
@seqNum = MAX(SeqNum)
FROM
TxCyclePhase
WHERE
TxCycle = @TxCycleID
SET @seqNum = @seqNum + 1
END
ELSE
BEGIN
SET @seqNum = 1
END
PRINT 'New Seq Num: ' + CONVERT(Varchar(5),@seqNum)
--greate a new seq number under the same phase
INSERT INTO TxCyclePhase
(Type, seqNum, TxCycle)
VALUES
(@PhaseTypeID, @seqNum, @TxCycleID)
--get the new ID, this will be used for the Event
SELECT
@TxCyclePhaseID = Max(TxCyclePhaseID)
FROM
TxCyclePhase
DECLARE @isFinal int
IF @Draft = 'Yes'
BEGIN
SET @isFinal = 0
END
ELSE
BEGIN
SET @isFinal = 1
END
IF EXISTS(SELECT * FROM LoginPassword WHERE ClinID = @ClinicianID AND AccessID IN (1,3))
BEGIN
IF NOT EXISTS (SELECT * FROM ClinPat WHERE ClinID = @ClinicianID AND PatientID = @PatientID)
BEGIN
INSERT INTO
ClinPat
(ClinID, PatientID)
VALUES
(@ClinicianID, @PatientID)
END
END
INSERT INTO FormPat
(PatientID, ClinID, FormID, TxCyclePhase, Date, Final, DataCollectionDate)
VALUES
(@PatientID, @ClinicianID, @FormID, @TxCyclePhaseID, GETDATE(), @isFinal, @CompletedDate)
SELECT @NewEventID = Scope_Identity()
SELECT @NewEventID AS 'EventID'
我做错了什么?
https://stackoverflow.com/questions/56263390
复制相似问题