Accepted Values Stored in Variable

Question

I am trying to write a re-usable query and this is pseudo-code of what I am after. Is this possible to do in Sql Server 2008 or do I just need to write a different stored procedure for each possibility?

Declare @acceptedvalues varchar(max), @whole12 varchar(500)

if @whole12 = 'MNM'
Begin
  SET @acceptedvalues = 'One', 'Two', 'Three'
End

if @whole12 = 'ALS'
Begin
  Set @acceptedvalues = 'Five', 'Seven', 'Nine'
End

Select
persID
,case when numinput NOT IN (@acceptedvalues) then 'Two' end as [validnumber]
FROM mixednuts

EDIT
I am trying to use a table variable - and this is my syntax, but it gives me an error of

Msg 137, Level 16, State 1, Line 32
Must declare the scalar variable "@acceptedvalues".

Declare @whole12 varchar(500)

DECLARE @acceptedvalues AS TABLE 
(
    AValue int NOT NULL
)

Set @whole12 = 'MNM'

if @whole12 = 'MNM'
Begin
  INSERT INTO @acceptedvalues (AValue) 
  VALUES ('1'), ('2'), ('3')
End

if @whole12 = 'ALS'
Begin
  INSERT INTO @acceptedvalues (AValue)
  VALUES ('5'), ('6'), ('7')
End

Create Table #MixedNuts
(
  persid int
  ,numinput int
)

Insert Into #MixedNuts Values ('1', '5'), ('2', '10'), ('3', '2')

Select
persID
,case when numinput NOT IN (@acceptedvalues) then 'Two' end as [validnumber]
FROM #mixednuts

Answer 1

You need to declare @acceptedvalues as a table variable. Similar to the below.

DECLARE @acceptedvalues AS TABLE 
(
AValue VARCHAR(50) NOT NULL
)

INSERT INTO @acceptedvalues (AValue) 
VALUES ('One'), ('Two'), ('Three')

Then it will work as you have specified in the case statement.

Sorry, you need to query the @AcceptedValues.

WHEN numinput NOT IN (SELECT AValue FROM @AcceptedValues) THEN 'Two' END AS [validnumber]