Stored Procedure to determine status

• billross

  SSC Eights!

  Points: 972

  More actions

  October 12, 2008 at 12:17 pm

  #199155

  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?

  "What I lack in youth I make up for in immaturity!"
  Please visit my music site at http://woundedego.com

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 12, 2008 at 12:19 pm

  #883919

  Duplicate post.

  Replies to the following thread please:

  http://www.sqlservercentral.com/Forums/Topic584586-1292-1.aspx

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass