---------------------------------------------------------------------

-- Variables

---------------------------------------------------------------------

-- Declare a variable and initialize it with a value

DECLARE @i AS INT;

SET @i = 10;

GO

-- Declare and initialize a variable in the same statement

DECLARE @i AS INT = 10;

GO

-- Store the result of a subquery in a variable

DECLARE @empname AS NVARCHAR(31);

SET @empname = (SELECT firstname + N' ' + lastname

                FROM HR.Employees

                WHERE empid = 3);

SELECT @empname AS empname;

GO

-- Using the SET command to assign one variable at a time

DECLARE @firstname AS NVARCHAR(10), @lastname AS NVARCHAR(20);

SET @firstname = (SELECT firstname

                  FROM HR.Employees

                  WHERE empid = 3);

SET @lastname = (SELECT lastname

                  FROM HR.Employees

                  WHERE empid = 3);

SELECT @firstname AS firstname, @lastname AS lastname;

GO

-- Using the SELECT command to assign multiple variables in the same statement

DECLARE @firstname AS NVARCHAR(10), @lastname AS NVARCHAR(20);

SELECT

  @firstname = firstname,

  @lastname  = lastname

FROM HR.Employees

WHERE empid = 3;

SELECT @firstname AS firstname, @lastname AS lastname;

GO

-- SELECT doesn't fail when multiple rows qualify

DECLARE @empname AS NVARCHAR(31);

SELECT @empname = firstname + N' ' + lastname

FROM HR.Employees

WHERE mgrid = 2;

SELECT @empname AS empname;

GO

-- SET fails when multiple rows qualify

DECLARE @empname AS NVARCHAR(31);

SET @empname = (SELECT firstname + N' ' + lastname

                FROM HR.Employees

                WHERE mgrid = 2);

SELECT @empname AS empname;

GO

-- The IF ... ELSE Flow Element

IF YEAR(SYSDATETIME()) <> YEAR(DATEADD(day, 1, SYSDATETIME()))

  PRINT 'Today is the last day of the year.';

ELSE

  PRINT 'Today is not the last day of the year.';

GO

-- IF ELSE IF

IF YEAR(SYSDATETIME()) <> YEAR(DATEADD(day, 1, SYSDATETIME()))

  PRINT 'Today is the last day of the year.';

ELSE

  IF MONTH(SYSDATETIME()) <> MONTH(DATEADD(day, 1, SYSDATETIME()))

    PRINT 'Today is the last day of the month but not the last day of the year.';

  ELSE

    PRINT 'Today is not the last day of the month.';

GO

-- Statement Block

IF DAY(SYSDATETIME()) = 1

BEGIN

  PRINT 'Today is the first day of the month.';

  PRINT 'Starting first-of-month-day process.';

  /* ... process code goes here ... */

  PRINT 'Finished first-of-month-day database process.';

END

ELSE

BEGIN

  PRINT 'Today is not the first day of the month.';

  PRINT 'Starting non-first-of-month-day process.';

  /* ... process code goes here ... */

  PRINT 'Finished non-first-of-month-day process.';

END

GO

-- The WHILE Flow Element

DECLARE @i AS INT = 1;

WHILE @i <= 10

BEGIN

  PRINT @i;

  SET @i = @i + 1;

END;

GO

-- BREAK

DECLARE @i AS INT = 1;

WHILE @i <= 10

BEGIN

  IF @i = 6 BREAK;

  PRINT @i;

  SET @i = @i + 1;

END;

GO

-- CONTINUE

DECLARE @i AS INT = 0;

WHILE @i < 10

BEGIN

  SET @i = @i + 1;

  IF @i = 6 CONTINUE;

  PRINT @i;

END;

GO

-- An Example of Using IF and WHILE

SET NOCOUNT ON;

IF OBJECT_ID('dbo.Numbers', 'U') IS NOT NULL DROP TABLE dbo.Numbers;

CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY);

GO

DECLARE @i AS INT = 1;

WHILE @i <= 1000

BEGIN

  INSERT INTO dbo.Numbers(n) VALUES(@i);

  SET @i = @i + 1;

END

GO

-- Example: Running Aggregations

SET NOCOUNT ON;

DECLARE @Result TABLE

(

  custid     INT,

  ordermonth DATETIME,

  qty        INT,

  runqty     INT,

  PRIMARY KEY(custid, ordermonth)

);

DECLARE

  @custid     AS INT,

  @prvcustid  AS INT,

  @ordermonth DATETIME,

  @qty        AS INT,

  @runqty     AS INT;

DECLARE C CURSOR FAST_FORWARD /* read only, forward only */ FOR

  SELECT custid, ordermonth, qty

  FROM Sales.CustOrders

  ORDER BY custid, ordermonth;

OPEN C;

FETCH NEXT FROM C INTO @custid, @ordermonth, @qty;

SELECT @prvcustid = @custid, @runqty = 0;

WHILE @@FETCH_STATUS = 0

BEGIN

  IF @custid <> @prvcustid

    SELECT @prvcustid = @custid, @runqty = 0;

  SET @runqty = @runqty + @qty;

  INSERT INTO @Result VALUES(@custid, @ordermonth, @qty, @runqty);

  FETCH NEXT FROM C INTO @custid, @ordermonth, @qty;

END

CLOSE C;

DEALLOCATE C;

SELECT

  custid,

  CONVERT(VARCHAR(7), ordermonth, 121) AS ordermonth,

  qty,

  runqty

FROM @Result

ORDER BY custid, ordermonth;

GO

SELECT custid, ordermonth, qty,

  SUM(qty) OVER(PARTITION BY custid

                ORDER BY ordermonth

                ROWS UNBOUNDED PRECEDING) AS runqty

FROM Sales.CustOrders

ORDER BY custid, ordermonth;