--This is a single line comment

/*
This is a 
multi-line comment
*/ 

DECLARE @intvariable    INT,
        @datevvariable  DATE

DECLARE @tablevar       TABLE
(ID         INT         NOT NULL,
Customer    VARCHAR(50) NOT NULL)
GO


DECLARE @intvariable    INT = 2,
        @datevvariable  DATE = GETDATE(),
        @maxorderdate   DATE = (SELECT MAX(OrderDate) FROM Orders.OrderHeader),
        @counter1       INT,
        @counter2       INT

SET @counter1 = 1
SELECT @counter2 = -1

SELECT @intvariable, @datevariable, @maxorderdate, @counter1, @counter2
GO

--SQL Server 2005 and below
DECLARE @var    INT

SET @var = 1
SET @var = @var + 1
SELECT @var
SET @var = @var * 2
SELECT @var
SET @var = @var / 4
SELECT @var
GO

--SQL Server 2008
DECLARE @var    INT

SET @var = 1
SET @var += 1
SELECT @var
SET @var *= 2
SELECT @var
SET @var /= 4
SELECT @var
GO

CREATE PROCEDURE <procedure name> @parm1  INT, @parm2 VARCHAR(20) = 'Default value'
AS
    --Code block
GO

CREATE PROCEDURE <procedure name> @parm1  INT, @parm2 VARCHAR(20) = 'Default value',
    @orderid    INT OUTPUT
AS
    --Code block
GO

CREATE PROCEDURE <procedure name> @parm1  INT, @parm2 VARCHAR(20) = 'Default value'
AS
    --This will return the results of this query to an application
    SELECT OrderID, CustomerID, OrderDate, SubTotal, TaxAmount, ShippingAmount, GrandTotal
    FROM Orders.OrderHeader
GO

CREATE PROCEDURE <procedure name> @parm1  INT, @parm2 VARCHAR(20) = 'Default value'
AS
    --This will return the value 1 back to the caller
    RETURN 1
GO

CREATE PROCEDURE <procedure name> @parm1  INT, @parm2 VARCHAR(20) = 'Default value'
AS
    --This will return the value 1 back to the caller
    RETURN 1

    --Any code from this point on will not be executed
GO

DECLARE @var    INT

SET @var = 1

IF @var = 1
    PRINT 'This is the code executed when true.'
ELSE
    PRINT 'This is the code executed when false.'
GO

DECLARE @var    INT

SET @var = 1

IF @var = 2
    PRINT 'This is the code executed when true.'
    PRINT 'This will always execute.'
GO

DECLARE @var    INT

SET @var = 1

IF @var = 2
BEGIN
    PRINT 'This is the code executed when true.'
    PRINT 'This code will also execute only when the conditional is true.'
END
GO

DECLARE @var1   INT,
        @var2   VARCHAR(30)

SET @var1 = 1

WHILE @var1 <= 10
BEGIN
    SET @var2 = 'Iteration #' + CAST(@var1 AS VARCHAR(2))

    PRINT @var2
    
    SET @var1 += 1
END
GO

DECLARE @var1   INT,
        @var2   VARCHAR(30)

SET @var1 = 1

--Pause for 2 seconds
WAITFOR DELAY '00:00:02'

WHILE @var1 <= 10
BEGIN
    SET @var2 = 'Iteration #' + CAST(@var1 AS VARCHAR(2))

    PRINT @var2
    
    SET @var1 += 1
END
GO

--Transaction errors
CREATE TABLE dbo.mytable
(ID		INT		NOT NULL PRIMARY KEY)

BEGIN TRAN
    INSERT INTO dbo.mytable VALUES(1)
    INSERT INTO dbo.mytable VALUES(1)
    INSERT INTO dbo.mytable VALUES(2)
COMMIT TRAN

SELECT * FROM dbo.mytable

TRUNCATE TABLE dbo.mytable
GO

SET XACT_ABORT ON;
BEGIN TRAN
    INSERT INTO dbo.mytable VALUES(1)
    INSERT INTO dbo.mytable VALUES(1)
    INSERT INTO dbo.mytable VALUES(2)
COMMIT TRAN
SET XACT_ABORT OFF;

SELECT * FROM dbo.mytable
GO

--TRY...CATCH
TRUNCATE TABLE dbo.mytable

BEGIN TRY
    BEGIN TRAN
        INSERT INTO dbo.mytable VALUES(1)
        INSERT INTO dbo.mytable VALUES(1)
        INSERT INTO dbo.mytable VALUES(2)
    COMMIT TRAN
END TRY

BEGIN CATCH
    ROLLBACK TRAN
    PRINT 'Catch'
END CATCH

SELECT * FROM dbo.mytable
GO

EXEC('SELECT OrderID, CustomerID FROM Orders.OrderHeader WHERE OrderID = 1')
GO

DECLARE @var    VARCHAR(MAX)

SET @var = 'SELECT OrderID, CustomerID FROM Orders.OrderHeader WHERE OrderID = 1'
EXEC(@var)
GO

EXEC sp_executesql N'SELECT OrderID, CustomerID FROM Orders.OrderHeader WHERE OrderID = 1'
GO

DECLARE @var    NVARCHAR(MAX)

SET @var = 'SELECT OrderID, CustomerID FROM Orders.OrderHeader WHERE OrderID = 1'
EXEC sp_executesql @var
GO

DECLARE curproducts CURSOR FAST_FORWARD FOR
    SELECT ProductID, ProductName, ListPrice FROM Products.Product
GO

DECLARE curproducts CURSOR READ_ONLY FOR
    SELECT ProductID, ProductName, ListPrice FROM Products.Product    
GO

DECLARE curproducts CURSOR FOR
    SELECT ProductID, ProductName, ListPrice FROM Products.Product
FOR READ ONLY
GO

DECLARE curproducts CURSOR FOR
    SELECT ProductID, ProductName, ListPrice FROM Products.Product
FOR READ ONLY

OPEN curproducts
GO

DECLARE @ProductID      INT,
        @ProductName    VARCHAR(50),
        @ListPrice      MONEY
        
DECLARE curproducts CURSOR FOR
    SELECT ProductID, ProductName, ListPrice FROM Products.Product
FOR READ ONLY

OPEN curproducts

FETCH curproducts INTO @ProductID, @ProductName, @ListPrice

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @ProductID, @ProductName, @ListPrice
    FETCH curproducts INTO @ProductID, @ProductName, @ListPrice
END

CLOSE curproducts
DEALLOCATE curproducts
GO