Must declare the scalar variable “@SAMPLE_STATUS”

Question

I need to create a stored procedure and update sample status depends on sample_status value returned from the system but I got this error when executing the procedure:

Msg 137, Level 15, State 2, Line 14
Must declare the scalar variable "@SAMPLE_STATUS".

This is the stored procedure:

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

Where to declare @SAMPLE_STATUS to solve this error?

Answer 1

Wouldn't it be simpler to write this follows?

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;

There may be some circumstances where this is not exactly the same as your logic (particularly if multiple rows match the WHERE conditions). But it seems to be what you intend.

The error in your code is not really obvious. I do recommend enclosing the body of the stored procedure in a BEGIN/END block; this can prevent some unexpected syntax errors.

Answer 2

There is nothing obviously wrong with your code, the variable @SAMPLE_STATUS seems to exist where its required.

However the procedure can be improved by using set based logic with procedural programming. The following update accomplishes the same thing using set based logic and hopefully also removes any errors:

CREATE PROCEDURE [dbo].[UPDATE_SAMPLE_DETAILS_STATUS]
(
  @ORDER_ID int
  , @TESTID int
  , @SAMPLE_STATUS int
)
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE [Lab_Hematology_Samples_Details]
    SET SAMPLE_STATUS = @SAMPLE_STATUS + 1
  WHERE ORDER_ID = @ORDER_ID
  AND TESTID = @TESTID
  AND @SAMPLE_STATUS < 6;
END