CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2008-r2
John Smith
John Smith

Reputation: 59

Multiple Assignment of more than one variable sql server select statement

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.

Upvotes: 1

Views: 407

Answers (2)

Nick Kavadias
Nick Kavadias

Reputation: 7338

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)

Upvotes: 3

Hannah Vernon
Hannah Vernon

Reputation: 3472

Wrap the assignments in BEGIN... END

Upvotes: 1

Related Questions