Stored Procedure help 1

[Databaseguy]

I'm using

substring([tblSearsItem].[Style],3,1)

to strip out the third character as a field. The result will be 'B', 'M', 'J', or 'T'

When the result is B, I want to display "Boys" in the SP as the column 'Gender'

When the result is M, I want to display "Mens" in the SP as the column 'Gender

Pretty much the same with the other two values.

I tried

CASE substring([tblSearsItem].[Style],3,1) WHEN 'B' THEN 'BOYS' END as [GENDER]
CASE substring([tblSearsItem].[Style],3,1) WHEN 'M' THEN 'MENS' END as [GENDER]

But it displays a column for each case. I only want one column that displays the same field name (Gender) and changes the value in the column based on the 3rd digit stripped out of the style field.

Any ideas ?

 

[Pattycake245]

How about

select gender =
CASE substring([tblSearsItem].[Style],3,1)
WHEN 'B' THEN 'BOYS'
WHEN 'M' THEN 'MENS'
WHEN 'J' THEN 'WHATEVER'
WHEN 'T' THEN 'WHATEVER' END

Tim

 

[Databaseguy]

Pattycake, you got me close. Here's a star. Thx

Here's what worked

CASE substring([tblSearsItem].[Style],3,1)
WHEN 'B' THEN 'BOYS'
WHEN 'T' THEN 'TODDLER'
END
AS [gender]