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