Accepted Values Stored in Variable 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
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]