0

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
0

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]
  • Would I need an individual table variable for each different @whole12? – MasterOfStupidQuestions Mar 29 '16 at 17:04
  • 1
    No, declare the table variable once and then populate it inside the IF/THEN blocks as appropriate. – Jonathan Fite Mar 29 '16 at 17:07
  • I keep getting an error - see my edit... – MasterOfStupidQuestions Mar 29 '16 at 17:13
  • Sorry, forgot to include that you need to change your case statement. see update to my answer. – Jonathan Fite Mar 29 '16 at 17:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.