Multiple Assignment of more than one variable sql server select statement

Question

Can I assign multiple variables under a single condition in a select statement in SQL Server (I am too stupid for dynamic sql):

For example in Visual Basic or many other languages you might have

If x=1 then z=4 y=2 End if

I don't think CASE works, because I think you can only assign to one variable.

Nick Kavadias · Accepted Answer · 2013-12-05 04:29:30Z

3

The trick is that if you can get away with the assignments on one line, then you dont need BEGIN/END block as the other person has suggested. Although things will start to look ugly. Here's some examples that achieve what you want:

-- using set syntax on one line
declare @x int, @y int , @z int
set @x=1
IF @x=1 set @y=2 set @z=4
print 'y:' + cast(@y as varchar) + ' z:' + cast(@z as varchar)
GO

-- using select syntax, a little prettier on one line
declare @x int, @y int , @z int
set @x=1
IF @x=1 SELECT @y=2,@z=4
print  'y:' + cast(@y as varchar) + ' z:' + cast(@z as varchar)
GO
-- using being/end
declare @x int, @y int , @z int
set @x=1
IF @x=1 
BEGIN
    set @y=2
    set @z=4
END
print 'y:' + cast(@y as varchar) + ' z:' + cast(@z as varchar)

answered Dec 5, 2013 at 4:29

Nick Kavadias

7,4283 gold badges40 silver badges47 bronze badges

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

1 Comment

Aaron Bertrand Over a year ago

Just please don't cast as varchar without length.

Hannah Vernon · Accepted Answer · 2013-12-05 01:10:05Z

1

Wrap the assignments in BEGIN... END

answered Dec 5, 2013 at 1:10

Hannah Vernon

3,4711 gold badge34 silver badges53 bronze badges

Collectives™ on Stack Overflow

Multiple Assignment of more than one variable sql server select statement

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related