Stored Procedure to determine status

Question

Post reply

Stored Procedure to determine status

billross

SSC Eights!

Points: 972
More actions
October 12, 2008 at 12:17 pm

#213979

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Ok...

Are you trying to return a resultset (from the select statement) or just a output variable?

The error is correct. In terms of the SET statement, the column CalculatedStatus does not exist. It only exists in the context of the prior select statement. They're two separate statements and nothing will carry over from the select to the set.

Unrelated, but still important:

Are the two columns CHOP ID and CONTRACT LEVEL nvarchar in the table? If not, the variables should match in data type with the columns as implicit conversions can cause performance problems. If they are nvarchar in the table, are you intending to store non-ascii characters in those columns?

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

billross SSC Eights! Points: 972 More actions · Answer 2

I'm trying to return just the calculated status variable.

I'll match the data type once I get this to compile and return a status.

Thanks for the s-p-e-e-d-y reply.

"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 · Answer 3

In that case, the proc should look like this

CREATE PROCEDURE dbo.sp_Chop_Status

@Chop_ID nVarChar(6) = NULL,

@Contract_Level nVarChar(1) = NULL,

@status nVarChar(20) OUTPUT

AS

/* SET NOCOUNT ON */

SELECT

@status = CASE

WHEN Spot = 'Y' THEN 'Spot'

ELSE 'To Arrive'

END

FROM DBO.CHOPS

WHERE [CHOP ID] = @CHOP_ID AND [CONTRACT LEVEL] = @Contract_Level

RETURN

For future reference, it's not recommended to create columns with spaces in the names. Makes code harder to write and harder to read

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

billross SSC Eights! Points: 972 More actions · Answer 4

Thanks.

What threw me off was that I was under the false impression that I had to select fields in the where clause even if I didn't need them.

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

billross SSC Eights! Points: 972 More actions · Answer 5

Thanks.

What threw me off was that I was under the false impression that I had to select fields in the where clause even if I didn't need them.

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