Declare multiple variables in SQL

Question

I am new to SQL and trying to determine how to set a variable to either A or B.
Here is the statement:

DECLARE @Planner AS VARCHAR(50) = '2566927' OR @Planner = '12201704'

And the error I am receiving:

The following errors occurred during execution of the SQL query:
Incorrect syntax near the keyword 'OR'.

Here is a more complete sample:

DECLARE
@Planner AS VARCHAR(50) = '2566927'

--Temp Table for Final
CREATE TABLE #PP1(
  Part_Key varChar(50)
  ,Part_No varChar(50)
  ,Part_Name varChar(50)
  ,CurInv DECIMAL(10,2)
  ,MinInv DECIMAL(10,2)
  ,Past_Due DECIMAL(10,2)
  ,Week2 DECIMAL(10,2)
  ,Week4 DECIMAL(10,2)
  ,Week8 DECIMAL(10,2)
  ,Week12 DECIMAL(10,2)
  ,Plus12 DECIMAL(10,2)
  ,Dep26w DECIMAL(10,1)
  ,Stock DECIMAL(10,1)
  ,StockPur DECIMAL (10,1)
  )

--Temp Table to Limit Parts
CREATE TABLE #MRP_Parts(
  MRP_PK varChar(50)
  ,MRP_PN varChar(50)
  ,MRP_PNAME varChar(50)
  )

--Insert into Temp Part Table
INSERT #MRP_Parts

SELECT

PP.Part_Key
,PP.Part_No
,PP.Name

FROM Part_v_Part AS PP
WHERE (PP.Planner = @Planner OR @Planner = '')

--BEGIN Temp Table for Inventory
CREATE TABLE #CurrInv(
  CI_Part_Key varChar(50)
  ,CI_Part_No varChar(50)
  ,CI_Qty DECIMAL(10,1)
  ,CI_Min DECIMAL(10,2)
  )

INSERT #CurrInv

SELECT

PP.PArt_Key
,PP.Part_No
,ISNULL(PC1.Quantity,0)
,PP.Minimum_Inventory_Quantity

FROM Part_v_Part AS PP
OUTER APPLY
(
  SELECT
    SUM(PC.Quantity) AS Quantity
  FROM Part_v_Container as PC
  WHERE PP.part_Key=PC.part_Key
    AND (PC.Container_Status = 'OK' 
      OR PC.Container_Status = 'Receiving'
      OR PC.Container_Status = 'Testing Hold')
    AND PC.Active = '1'
    AND (PP.Planner = @Planner OR @Planner = '')
) AS PC1

What I would like is for the @Planner to be either A or B

you cannot do this. please ask your question a different way — MichaelEvanchik
– MichaelEvanchik, Commented Feb 6, 2018 at 22:05
You cannot set a variable to "either A or B": it must have one value. Please edit explaining what you want to do — giorgiga
– giorgiga, Commented Feb 6, 2018 at 22:09
A variable holds a value. One value. What are you trying to do? — JNevill
– JNevill, Commented Feb 6, 2018 at 22:11
@user9324484 Make up your mind. Why would a variable have 2 values?? How would you know which value to assign??? It just doesn't make sense. — Eric
– Eric, Commented Feb 6, 2018 at 23:36

Disillusioned · Accepted Answer · 2018-02-06 22:38:17Z

23

A second variable must have a different name. E.g.

DECLARE  @Planner1 VARCHAR(50) = '2566927',
         @Planner2 varchar(10) = '12201704',
         @OtherVar int = 42

And separate each variable declaration with a comma ,

answered Feb 6, 2018 at 22:38

Disillusioned

14.9k3 gold badges47 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kirk Broadhurst · Accepted Answer · 2018-02-06 22:48:17Z

3

You can't do that, but you can declare a variable as a table and put multiple values into the table

DECLARE @Planner AS TABLE (P VARCHAR(50))
INSERT @Planner SELECT '2566927' 
INSERT @Planner SELECT '12201704'

And then you can use the table variable in a where in type clause

SELECT
    PP.Part_Key
    ,PP.Part_No
    ,PP.Name
FROM Part_v_Part AS PP
WHERE PP.Planner IN (SELECT P FROM @Planner)

answered Feb 6, 2018 at 22:48

Kirk Broadhurst

28.9k20 gold badges111 silver badges179 bronze badges

Collectives™ on Stack Overflow

Declare multiple variables in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related