Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Generate Random Password User-Defined Function
Home > SQL Server 2008 > Compound Operators
Compound Operators

SQL Server 2008 introduces the Compound Operators as a programmability enhancement in Transact-SQL.  Compound operators execute some operation, such as +, -, * and /, and set an original value to the result of the operation.  It is just a simpler syntax that you can use on commonly performed operation such as incrementing a variable by a certain number.

As an example, instead of doing the following:

SET @Index = @Index + 1

it can now simplified as follows:

SET @Index += 1

The following are the new compound operators that are available:

Compound Operator Old Syntax New Syntax
+= (Add EQUALS)
SET @Index = @Index + 1
SET @Index += 1
-= (Subtract EQUALS)
SET @Step = @Step - 1
SET @Step -= 1
*= (Multiply EQUALS)
SET @TotalPriceWithTax = @TotalPriceWithTax * 1.06
SET @TotalPriceWithTax *= 1.06
/= (Divide EQUALS)
SET @Level1 = @Level1 / @Total
SET @Level1 /= @Total
%= (Modulo EQUALS)
SET @Remainder = @Remainder % 3
SET @Remainder /= 3
&= (Bitwise AND EQUALS)
SET @BitwiseAND = @BitwiseAND & 8
SET @BitwiseAND &= 8
^= (Bitwise Exclusive OR EQUALS)
SET @BitwiseXOR = @BitwiseXOR ^ 128
SET @BitwiseXOR ^= 128
|= (Bitwise OR EQUALS)
SET @BitwiseOR = @BitwiseOR | 16
SET @BitwiseOR |= 16


Sample Usage - Comma-Delimited Output (CSV) Without the Use of Cursors

Let's now show an example on where the compound operator can be used.  There is an article in the Tips & Tricks section where a comma-delimited output is generated from a table without the use of a cursor (see article here).  We will create a similar function that will use the += (add EQUALS) compound operator. 

We have a table of Major League Teams, such as New York Yankees and Boston Red Sox, and a table of starting pitchers.

CREATE TABLE [dbo].[MLBTeam] (
    [TeamCode] CHAR(3),
    [TeamName] VARCHAR(50)
)
GO

CREATE TABLE [dbo].[StartingPitcher] (
    [TeamCode] CHAR(3),
    [FirstName] VARCHAR(50),
    [LastName] VARCHAR(50)
)
GO

For each MLB team in the [dbo].[MLBTeam] table we want to list their starting pitchers in alphabetical order with the following output format:

Team Name               Starting Pitchers
----------------------- --------------------------------------------------
Boston Red Sox          Beckett, Matsuzaka, Lester, Penny, Wakefield
Los Angeles Dodgers     Billingsley, Kershaw, Kuroda, Milton, Wolf
New York Yankees        Burnett, Chamberlain, Pettite, Sabathia, Wang
Philadelphia Phillies   Bastardo, Blanton, Hamels, Happ, Moyer

What we need to do first is to create a user-defined function that will accept as parameter the MLB team code and return as an output the list of starting pitchers in a comma-delimited string.  Here's how the user-defined function which uses the += (Add/EQUALS) compound operator.

CREATE FUNCTION [dbo].[mlbfn_GetStartingPitchers] (
	@TeamCode		CHAR(3)
)
RETURNS VARCHAR(500)
AS
BEGIN
	DECLARE @StartingPitchers			VARCHAR(500)

	SET @StartingPitchers = ''

	SELECT @StartingPitchers += ', ' + [LastName]
	FROM [dbo].[StartingPitcher]
	WHERE [TeamCode] = @TeamCode
	ORDER BY [LastName]

	RETURN SUBSTRING(@StartingPitchers, 3, LEN(@StartingPitchers))
END
GO

Once the user-defined function is done, we simply need to call it and passing the MLB team code as the parameter.  Here's how the SELECT statement will look like:

SELECT [TeamName], [dbo].[mlbfn_GetStartingPitchers] ( [TeamCode] ) AS [StartingPitchers]
FROM [dbo].[MLBTeam] 
ORDER BY [TeamName]

The output of this SELECT statement will be as shown above, which I repeat below:

Team Name               Starting Pitchers
----------------------- --------------------------------------------------
Boston Red Sox          Beckett, Matsuzaka, Lester, Penny, Wakefield
Los Angeles Dodgers     Billingsley, Kershaw, Kuroda, Milton, Wolf
New York Yankees        Burnett, Chamberlain, Pettite, Sabathia, Wang
Philadelphia Phillies   Bastardo, Blanton, Hamels, Happ, Moyer
Related Articles :