There are many situations when you have a variable in a stored procedure and you want to add to it. Well, there is definately a slick way to build on variables within a single select statement. It’s necessary to reveal how this works in detail because it’s difficult to explain. So, the answer is in the statement.
DECLARE @Number1 numeric(14,2)
DECLARE @Number2 numeric(14,2)
DECLARE @Number3 numeric(14,2)
DECLARE @Total numeric(14,2)
-- Step 1: One way to to set variables is like this, on individual lines
/*
SET @Number1 = 15
SET @Number2 = 25
SET @Number3 = 50
*/
-- Step 1: or, as I like to do it, use the SELECT because I can assign multiple variables at once.
SELECT @Number1 = 15
, @Number2 = 25
, @Number3 = 50
-- Step 2: then, add the numbers together traditionally.
SELECT @Total = @Number1 + @Number2 + @Number3
SELECT [Total] = @Total
In a recent project it was necessary to grab a lot of numbers from multiple tables, perform calculations with them, and then return the results. The trick to assign multiple variables at once as indicated above yields the results of the simple trick. Instead of adding all the numbers in separate lines, do it all in one SELECT statement. Fast efficient results.
SELECT @Number1 = 15
, @Number2 = 25
, @Number3 = 50
, @Total = @Number1 + @Number2 + @Number3
SELECT [Total] = @Total
It’s great that I can reference previous variables without having to write individual SELECT or SET statements. Makes for very slick code. Just make sure that if you move the variables around, they stay in the descending order of dependency or your numbers will be off.