|
|
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
|