Multiple variables assignment using SET and SELECT

Question

I use the following t-sql code for assign to variable a value obtained from select statement

 DECLARE @cfMitt nvarchar(16)

 SET @cfMitt = (SELECT CfMittente
    FROM Messaggi
    WHERE IDMessaggio = @IDMessaggio)

If I want use multiple assignement I try with the following code, but something is wrong:

DECLARE @cfMitt nvarchar(16)
DECLARE @cfDest nvarchar(16)

SET @cfMitt, @cfDest= (SELECT CfMittente, CfDestinatario
FROM Messaggi
WHERE IDMessaggio = @IDMessaggio)

Where is the error?

podiluska · Accepted Answer · 2012-11-05 14:49:06Z

6

Set only assigns one value at a time.

You should use

   SELECT @cfMitt = CfMittente, 
          @cfDest = CfDestinatario
   FROM Messaggi
   WHERE IDMessaggio = @IDMessaggio

answered Nov 5, 2012 at 14:49

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

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

Comments

Indi · Accepted Answer · 2012-11-05 14:50:16Z

1

Variable declaration can also be;

DECLARE @V1 VarType, @V2 VarType,...

Assignment;

SELECT @V1 = C1, @V2 = C2,...@Vn = Cn
FROM [Table]
WHERE Conditions

answered Nov 5, 2012 at 14:50

Indi

33.9k7 gold badges61 silver badges80 bronze badges

Collectives™ on Stack Overflow

Multiple variables assignment using SET and SELECT

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