sql - 必须声明标量变量“@SAMPLE_STATUS”
问题描述
我需要创建一个存储过程并更新样本状态取决于从系统返回的 sample_status 值,但在执行该过程时出现此错误:
消息 137,级别 15,状态 2,第 14 行
必须声明标量变量“@SAMPLE_STATUS”。
这是存储过程:
CREATE PROCEDURE [dbo].[UPDATE_SAMPLE_DETAILS_STATUS]
@ORDER_ID int,
@TESTID int,
@SAMPLE_STATUS int
AS
IF (@SAMPLE_STATUS = 1)
BEGIN
UPDATE [Lab_Hematology_Samples_Details]
SET SAMPLE_STATUS = 2
WHERE ORDER_ID = @ORDER_ID
AND testid = @testid
END
ELSE IF (@SAMPLE_STATUS = 2)
BEGIN
UPDATE [Lab_Hematology_Samples_Details]
SET SAMPLE_STATUS = 3
WHERE ORDER_ID = @ORDER_ID
AND testid = @testid
END
ELSE IF (@SAMPLE_STATUS = 3)
BEGIN
UPDATE [Lab_Hematology_Samples_Details]
SET SAMPLE_STATUS = 4
WHERE ORDER_ID = @ORDER_ID
AND testid = @testid
END
ELSE IF (@SAMPLE_STATUS = 4)
BEGIN
UPDATE [Lab_Hematology_Samples_Details]
SET SAMPLE_STATUS = 5
WHERE ORDER_ID = @ORDER_ID
AND testid = @testid
END
ELSE IF (@SAMPLE_STATUS = 5)
BEGIN
UPDATE [Lab_Hematology_Samples_Details]
SET SAMPLE_STATUS = 6
WHERE ORDER_ID = @ORDER_ID
AND testid = @testid
END
在哪里声明@SAMPLE_STATUS
来解决这个错误?
解决方案
写这个不是更简单吗?
create proc [dbo].[UPDATE_SAMPLE_DETAILS_STATUS] (
@ORDER_ID int,
@TESTID int,
@SAMPLE_STATUS int
) AS
BEGIN
UPDATE [Lab_Hematology_Samples_Details]
SET SAMPLE_STATUS = @SAMPLE_STATUS + 1
WHERE ORDER_ID = @ORDER_ID AND
testid = @testid AND
@SAMPLE_STATUS IN (1, 2, 3, 4, 5);
END;
在某些情况下,这可能与您的逻辑不完全相同(尤其是在多行符合WHERE
条件的情况下)。但这似乎是你想要的。
您的代码中的错误并不是很明显。我确实建议将存储过程的主体包含在BEGIN
/END
块中;这可以防止一些意外的语法错误。