This is my attempt to create a stored procedure that returns a status based on conditions in other fields. I set it up as a CASE statement since I plan on adding other criteria for other statuses once I get it working. It says that CaclulatedStatus is an invalid column name:
Invalid column name 'CalculatedStatus'.
CREATE PROCEDURE dbo.sp_Chop_Status
@Chop_ID nVarChar(6) = NULL,
@Contract_Level nVarChar(1) = NULL,
@Status nVarChar(20) OUTPUT
AS
BEGIN
/* SET NOCOUNT ON */
SELECT
[CHOP ID],
[CONTRACT LEVEL],
[ARRIVAL DATE],
SPOT,
CASE
WHEN Spot = 'Y' THEN 'Spot'
WHEN Spot <> 'Y' THEN 'To Arrive'
END
AS CalculatedStatus
FROM DBO.CHOPS
WHERE (([CHOP ID] = @CHOP_ID)
AND ([CONTRACT LEVEL] = @Contract_Level))
SET @Status = CalculatedStatus
END
RETURN
How do I return the status?