Now that you've learned the basics of using the T-SQL SET and SELECT statements to assign values to variables and concatenate variable string values, let's wade farther out and review how to use the basic math operators with T-SQL variables.
T-SQL has built-in operators for all the basic mathematic functions, including addition, subtraction, multiplication, and division. The code below illustrates how to use T-SQL’s mathematic functions with variables.
DECLARE @NUMVAR1 INT DECLARE @NUMVAR2 INT SET @NUMVAR1 = 1 SET @NUMVAR2 = 2 SELECT @NUMVAR1 + @NUMVAR2 SELECT @NUMVAR2 - @NUMVAR1 SELECT @NUMVAR2 * @NUMVAR2 SELECT @NUMVAR2 / @NUMVAR1 SELECT @NUMVAR1 % @NUMVAR2 SELECT (@NUMVAR2 * @NUMVAR2) + 3
This sample code first declares two variables, @NUMVAR1 and @NUMVAR2, and assigns them a value of 1 and 2, respectively. The subsequent lines each contain an expression that illustrates the use of (in order) addition, subtraction, multiplication, division, modulus (remainder, indicated by the percent sign--%), and precedence. SQL Server Express uses the same precedence, or order, of evaluating complex mathematical expressions that you learned in grade school, calculating first the portions within parentheses, followed by exponents, multiplication, addition, and subtraction. Running the sample T-SQL code returns the following result for the first through sixth expression, respectively: 3, 1, 4, 2, 1, 7.
