---------------------------------------------------------------------
-- SQL Server 2005XgAhvV[WvO~O
-- 3 Transact-SQL̃vO~O\̊b
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 3.2 f[^x[XIuWFNg̏Cq
---------------------------------------------------------------------

SQLBox.Asset5.dbo.ap_Eq_List


ap_Eq_List
dbo.ap_Eq_List
Asset5.dbo.ap_Eq_List
Asset5..ap_Eq_List
SQLBox.Asset5..ap_Eq_List
SQLBox...ap_Eq_List
SQLBox..dbo.ap_Eq_List

---------------------------------------------------------------------
-- 3.3 f[^^
---------------------------------------------------------------------

CREATE TABLE Contacts(
    ContactId CHAR(8),
    Name VARCHAR(50),
    Note TEXT,
    Resume VARCHAR(max)
)


INSERT INTO Contacts(ContactId, Name, Note, Resume)
VALUES('CO-92-81', 'Tom Jones', 'Tom@trigon.com', 'N/A')


CREATE TABLE Contacts_2(
    ContactId NCHAR(8),
    Name NVARCHAR(50),
    Note NTEXT,
    Resume NVARCHAR(max)
)


INSERT INTO Contacts_2(ContactId, Name, Note, Resume)
VALUES(N'CO-92-81', N'Tom Jones', N'Tom@trigonblue.com', N'N/A')


CREATE TABLE Contacts_3(
    ContactId NCHAR(8),
    Name NVARCHAR(50),
    DateOfBirth SMALLDATETIME,
    Resume NVARCHAR(max)
)

INSERT INTO Contacts_3(ContactId, Name, DateOfBirth, Resume)
VALUES(N'CO-92-81', N'Tom Jones', '1965/2/21 10:03 AM', N'Tom@trigonblue.com')


SELECT *
FROM Contacts_3
WHERE DateOfBirth = '1965/2/21'


SELECT *
FROM Contacts_3
WHERE DateOfBirth >= '1965/2/21'
AND DateOfBirth < '1965/2/22'


UPDATE Inventory
SET StatusId = 3
WHERE InventoryId = 1042


CREATE TABLE Patient(
    PatientId INT,
    FullName VARCHAR(30),
    Weight DECIMAL(5, 2),
    Height DECIMAL(5, 2),
    ADP SMALLINT,
    BDZ TINYINT
)


INSERT INTO Patient(PatientId, FullName, Weight, Height, ADP, BDZ)
VALUES(834021, 'Tom Jones', 89.5, 188.5, 450, 11)


UPDATE Inventory
SET Rent = $0
WHERE InventoryId = 1042


CREATE TABLE MyTable(
    Id INT,
    BinData VARBINARY(8000),
    Diagram VARBINARY(max)
)


UPDATE MyTable
SET BinData = 0x82A7210B
WHERE Id = 121131


UPDATE dbo.MyTable
SET Diagram =
(
    SELECT *
    FROM OPENROWSET
    (
        BULK 'C:\My Pictures\desktop.bmp', SINGLE_BLOB
    ) AS a
)
WHERE Id = 121131


CREATE TABLE dbo.Eq2(
    EqId INT,
    EqCompList XML
)


INSERT INTO dbo.Eq2(EqId, EqCompList)
VALUES(123, '<CompList><CZ101/><AZ401/><BZ407/></CompList>')


CREATE TABLE Lookup(
    LookupGroupId TINYINT,
    LookupId SMALLINT,
    LookupValue SQL_VARIANT
)


INSERT Lookup(LookupGroupId, LookupId, LookupValue)
VALUES(2, 34, 'VAR')

INSERT Lookup(LookupGroupId, LookupId, LookupValue)
VALUES(3, 22, 2000)

INSERT Lookup(LookupGroupId, LookupId, LookupValue)
VALUES(4, 16, '1/12/2000')

INSERT Lookup(LookupGroupId, LookupId, LookupValue)
VALUES(4, 11, $50000)


CREATE TYPE Phone FROM VARCHAR(20) NOT NULL
CREATE TYPE typPostalCode FROM VARCHAR(7) NULL

---------------------------------------------------------------------
-- 3.4 ϐ
---------------------------------------------------------------------

DECLARE @LastName VARCHAR(50)


DECLARE @LastName VARCHAR(50),
        @FirstName VARCHAR(30),
        @BirthDate SMALLDATETIME


DECLARE @OfficePhone phone


SELECT @LastName = 'Smith'


SELECT @LastName = 'Smith',
       @FirstName = 'David',
       @BirthDate = '2/21/1965'


SELECT @Make = Equipment.Make,
       @Model = Equipment.Model,
       @EqType = EqType.EqType
FROM EqType INNER JOIN Equipment
    ON EqType.EqTypeId = Equipment.EqTypeId
WHERE Equipment.EqId = 2


DECLARE @make VARCHAR(50),
        @model VARCHAR(50),
        @EqType VARCHAR(50)

SELECT @Make = 'ACME',
       @Model = 'Turbo',
       @EqType = 'cabadaster'

SELECT @Make = make,
       @Model = Model,
       @EqType = EqType.EqType
FROM EqType INNER JOIN Equipment
    ON EqType.EqTypeId = Equipment.EqTypeId
WHERE EqId = -1

SELECT @make make, @model model, @EqType EqType


SELECT @Make = Make,
       @Model = Model,
       @EquipmentName = Make + ' ' + Model,
       @EqType = (SELECT EqType
                  FROM dbo.EqType EqType
                  WHERE EqType.EqTypeId = Equipment.EqTypeId)
FROM dbo.Equipment
WHERE EqId = 2


SELECT Make,
       @Model = Model -- ͌
FROM dbo.Equipment
WHERE EqId = 2


SET @LastName = 'Johnson'


SET @Make = 'ACME'
SET @Model = 'Turbo'
SET @EqType = 'cabadaster'


UPDATE Inventory
SET @mnsCost = Cost = Cost * @fltTaxRate
WHERE InventoryId = @intInventoryId


SELECT @LastName
PRINT @FirstName


SELECT Make "Selected Make",
       Model "Selected Model",
       @Model "Original Model"
FROM Equipment
WHERE EqId = 2


CREATE TABLE Eq(
    EqId INT IDENTITY(1, 1),
    Make VARCHAR(50),
    Model VARCHAR(50),
    EqTypeId INT
)


DECLARE @intEqId INT
INSERT INTO Eq(Make, Model, EqTypeId)
VALUES ('ACME', 'Turbo', 2)
SET @intEqId = @@IDENTITY


DECLARE @intEqId INT
INSERT INTO Eq(Make, Model, EqTypeId)
    SELECT Make, Model, EqTypeID
    FROM Equipment
SELECT @intEqId = @@IDENTITY


UPDATE Equipment
SET EqTypeId = 3
WHERE EqTypeId = 2
SELECT @intErrorCode = @@ERROR


DECLARE @intEqId INT,
        @intErrorCode INT
INSERT INTO Equipment(Make, Model, EqTypeId)
VALUES('ACME', 'Turbo', 2)
SELECT @intEqId = @@IDENTITY,
       @intErrorCode = @@ERROR


SELECT Make, Model, EqTypeid
INTO OldEquipment
FROM Equipment
WHERE EqTypeid = 2

IF @@ROWCOUNT = 0
    PRINT N's̓Rs[܂ł'


DECLARE @MyTableVar TABLE
(
    Id INT PRIMARY KEY,
    Lookup VARCHAR(15)
)

INSERT @MyTableVar VALUES(1, '1Q2000')
INSERT @MyTableVar VALUES(2, '2Q2000')
INSERT @MyTableVar VALUES(3, '3Q2000')
SELECT * FROM @MyTableVar


SELECT LookupId, Lookup
INTO @TableVariable -- ͌
FROM Lookup


INSERT INTO @TableVariable -- ͌
    EXEC prMyProcedure

---------------------------------------------------------------------
-- 3.5 t[Xe[gg
---------------------------------------------------------------------

SELECT LeaseId, LeaseVendor --, LeaseNumber
FROM dbo.Lease
WHERE ContractDate > '1/1/1999'


-- SELECT * FROM dbo.Equipment -- fobÔ߂̃RgAEg


/*
̍s̍sRg
sɂׂ͂Ė
*/

/* ׂĂ̔i\ */
SELECT * FROM Equipment


/*
-- ׂĂ̔i\
SELECT * FROM Equipment
*/


/* ̍s̓RgB
/* SQL Server 2000̃NGAiCÚA̋؂ŏ̃Rg̏I
߂邪ASQL Server 2005SQL Server Management Studioł
삷 */
   ꍇɂẮAō\G[ */
SELECT * FROM dbo.Equipment


/*****************************************************************
** t@CF ap_Equipment_Insert.sql
** OF ap_Equipment_Insert
** F iƂ̌`}
**       i݂Ȃꍇ̂݁j
**
** ߂lF ErrorCode
**
** ĂяoF   middleware
**
** p[^F
** Input                                  Output
** ----------                             -----------
** Make                                   EqId
** Model
** EqType
**
** ҁF Dejan Sunderic
** 쐬F 1/1/2005
******************************************************************
** C
******************************************************************
** tF       ƎҁF     Ɠe
** --------     --------     -------------------------------------
** 11/1/2005    DS           Fixed:49. G[C
** 11/2/2005    DS           Fixed:36. ptH[}XœK
*****************************************************************/


BEGIN
    INSERT [Order](OrderDate, RequestedById, TargetDate, DestinationLocation)
    VALUES(@OrderDate, @ContactId, @TargetDate, @LocId)
    SELECT @ErrorCode = @@ERROR

    IF @ErrorCode <> 0
    BEGIN
        RAISERROR(N'̑}ɃG[܂', 16, 1)
    END
END


IF @IntErrorCode= 0
BEGIN
    INSERT [Order](OrderDate, RequestedById, TargetDate, DestinationLocation)
    VALUES(@OrderDate, @ContactId, @TargetDate, @LocId)
    SELECT @ErrorCode = @@ERROR
    @intOrderId = @@IDENTITY
END


CREATE PROCEDURE dbo.ap_Equipment_Insert_1
-- Equipmente[uɒli[
-- ĂяoɃR[hIDԂ
(
    @chvMake VARCHAR(50),
    @chvModel VARCHAR(50),
    @chvEqType VARCHAR(30)
)
AS
    DECLARE @intEqTypeId INT,
            @intEqId INT
    -- EqTypeID擾
    SELECT @intEqTypeId = EqTypeId
    FROM dbo.EqType
    WHERE EqType = @chvEqType

    -- f[^x[XEqType݂邩ǂ𒲂ׂ
    IF @intEqTypeId IS NOT NULL
        -- ĩR[h}
        INSERT dbo.Equipment(Make, Model, EqTypeId)
        VALUES(@chvMake, @chvModel, @intEqTypeId)
    ELSE
        -- EqType݂ȂꍇAȉs
        BEGIN
            -- VEqTypef[^x[Xɒǉ
            INSERT dbo.EqType(EqType)
            VALUES(@chvEqType)
            -- ǉR[hID擾
            SELECT @intEqTypeId = @@IDENTITY
            -- ĩR[h}
            INSERT dbo.Equipment(Make, Model, EqTypeId)
            VALUES(@chvMake, @chvModel, @intEqTypeId)
       END
    SELECT @intEqId = @@IDENTITY
    -- ĂяoIDԂ
    RETURN @intEqId


CREATE PROCEDURE dbo.ap_Equipment_Insert_2
-- Equipmente[uɒli[
-- ĂяoɃR[hIDԂ
(
    @chvMake VARCHAR(50),
    @chvModel VARCHAR(50),
    @chvEqType VARCHAR(30)
)
AS
    DECLARE @intEqTypeId INT,
            @intEqId INT

    -- f[^x[XEqType݂邩ǂ𒲂ׂ
    IF NOT EXISTS (SELECT EqTypeId FROM dbo.EqType WHERE EqType = @chvEqType)
        -- EqType݂ȂꍇAȉs
        BEGIN
            -- VEqTypef[^x[Xɒǉ
            INSERT dbo.EqType(EqType)
            VALUES(@chvEqType)
            -- ǉR[hID擾
            SELECT @intEqTypeId = @@IDENTITY
        END
    ELSE
        -- EqTypeID擾
        SELECT @intEqTypeId = EqTypeId
        FROM dbo.EqType
        WHERE EqType = @chvEqType

    -- ĩR[h}
    INSERT dbo.Equipment(Make, Model, EqTypeId)
    VALUES(@chvMake, @chvModel, @intEqTypeId)

    SELECT @intEqId = @@IDENTITY
    -- ĂяoIDԂ
    RETURN @intEqId


CREATE PROCEDURE dbo.ap_Equipment_Insert_3
-- Equipmente[uɒli[
-- ĂяoɃR[hIDԂ
(
    @chvMake VARCHAR(50),
    @chvModel VARCHAR(50),
    @chvEqType VARCHAR(30)
)
AS
    DECLARE @intEqTypeId INT,
            @ErrorCode INT,
            @intEqId INT

    -- f[^x[XEqType݂邩ǂ𒲂ׂ
    IF NOT EXISTS (SELECT EqTypeId FROM dbo.EqType WHERE EqType = @chvEqType)
        -- EqType݂ȂꍇAȉs
        BEGIN
            -- VEqTypef[^x[Xɒǉ
            INSERT EqType(EqType)
            VALUES(@chvEqType)
            -- ǉR[hID擾
            SELECT @intEqTypeId = @@IDENTITY,
                   @ErrorCode = @@ERROR
            IF @ErrorCode <> 0
                BEGIN
                    SELECT N'i^ǉł܂łBG[F', @ErrorCode
                    RETURN -1
                END
        END
    ELSE
        -- EqTypeID擾
        SELECT @intEqTypeId = EqTypeId
        FROM dbo.EqType
        WHERE EqType = @chvEqType

    -- ĩR[h}
    INSERT Equipment(Make, Model, EqTypeId)
    VALUES(@chvMake, @chvModel, @intEqTypeId)
    -- ĂяoIDԂ
    SELECT @intEqId = @@IDENTITY,
           @ErrorCode = @@ERROR
    IF @ErrorCode <> 0
        BEGIN
            SELECT N'i}ł܂łBG[F', @ErrorCode
            RETURN -2
        END
    RETURN @intEqId


CREATE PROCEDURE ap_CalcFactorial
-- KvZ
-- 1! = 1
-- 3! = 3 * 2 * 1
-- n! = n * (n-1)* ... 5 * 4 * 3 * 2 * 1
    @inyN TINYINT,
    @intFactorial BIGINT OUTPUT
AS
    SET @intFactorial = 1
    WHILE @inyN > 1
    BEGIN
         SET @intFactorial = @intFactorial * @inyN
         SET @inyN = @inyN - 1
    END
    RETURN 0


CREATE PROCEDURE ap_InventoryProperties_Get
/************************************************************
ĩvpeBÂ悤ɃZ~Rŋ؂XgԂ
; Property=Value Unit; Property=Value Unit; Property=Value Unit; ...

eXg@:
DECLARE @p VARCHAR(max)
EXEC ap_InventoryProperties_Get 5, @p OUTPUT
SELECT @p
*************************************************************/
(
    @intInventoryId INT,
    @chvProperties VARCHAR(max) OUTPUT
)
AS
    DECLARE @intCountProperties INT,
            @intCounter INT,
            @chvProperty VARCHAR(50),
            @chvValue VARCHAR(50),
            @chvUnit VARCHAR(50)

    CREATE TABLE #Properties(
        Id INT IDENTITY(1, 1),
        Property VARCHAR(50),
        Value VARCHAR(50),
        Unit VARCHAR(50)
    )

    -- ĩvpeB肷
    INSERT INTO #Properties(Property, Value, Unit)
        SELECT Property, Value, Unit
        FROM dbo.InventoryProperty InventoryProperty
        INNER JOIN dbo.Property Property
            ON InventoryProperty.PropertyId = Property.PropertyId
        WHERE InventoryProperty.InventoryId = @intInventoryId

    -- [vݒ肷
    SELECT @intCountProperties = COUNT(*),
           @intCounter = 1,
           @chvProperties = ''
    FROM #Properties

    -- vpeBXgŜ[v
    WHILE @intCounter <= @intCountProperties
    BEGIN
        -- evpeB̒l擾
        SELECT @chvProperty = Property,
               @chvValue = Value,
               @chvUnit = Unit
        FROM #Properties
        WHERE Id = @intCounter

        -- Xg쐬
        SET @chvProperties = @chvProperties + '; '
                           + @chvProperty + '='
                           + @chvValue + ' '
                           + ISNULL(@chvUnit, '')

        -- Oɖ߂Ď̔i擾
        SET @intCounter = @intCounter + 1
    END
    IF SUBSTRING(@chvProperties, 0, 2) = '; '
        SET @chvProperties = RIGHT(@chvProperties, LEN(@chvProperties) - 2)

    DROP TABLE #Properties
    RETURN 0


CREATE PROCEDURE dbo.ap_Lease_Close
-- w̃[XԍɑΉĩ[X폜
    @intLeaseId INT
AS
    -- [XXV
    UPDATE dbo.Inventory
    SET Rent = 0,
        LeaseId = NULL,
        LeaseScheduleId = NULL
    WHERE LeaseId = @intLeaseId
    IF @@ERROR <> 0 GOTO PROBLEM_1

    -- XPW[폜
    DELETE FROM dbo.LeaseSchedule
    WHERE LeaseId = @intLeaseId
    IF @@ERROR <> 0 GOTO PROBLEM_2

    -- [X폜
    DELETE FROM dbo.Lease
    WHERE LeaseId = @intLeaseId
    IF @@ERROR <> 0 GOTO PROBLEM_3
    RETURN 0

    PROBLEM_1:
        SELECT N'[XXVł܂'
        RETURN 50001
    PROBLEM_2:
        SELECT N'f[^x[XXPW[폜ł܂'
        RETURN 50002
    PROBLEM_3:
        SELECT N'f[^x[X烊[X폜ł܂'
        RETURN 50003


WAITFOR DELAY '00:01:00'
    SELECT * FROM Equipment


WAITFOR TIME '23:00'
    BACKUP DATABASE Asset5 TO Asset_bkp


---------------------------------------------------------------------
-- 3.6 J[\
---------------------------------------------------------------------

CREATE PROCEDURE dbo.ap_InventoryProperties_Get_Cursor
/********************************************************************
ĩvpeBÂ悤ɃZ~Rŋ؂XgԂ
Property=Value Unit; Property=Value Unit; Property=Value Unit; ...

eXg:
DECLARE @p VARCHAR(max)
EXEC dbo.ap_InventoryProperties_Get_Cursor 5, @p OUTPUT
SELECT @p
********************************************************************/
(
    @intInventoryId INT,
    @chvProperties VARCHAR(max) OUTPUT
)
    AS
    DECLARE @intCountProperties INT,
            @intCounter INT,
            @chvProperty VARCHAR(50),
            @chvValue VARCHAR(50),
            @chvUnit VARCHAR(50)

    SET @chvProperties = ''

    DECLARE @CrsrVar CURSOR

    SET @CrsrVar = CURSOR FOR
        SELECT Property, Value, Unit
        FROM dbo.InventoryProperty InventoryProperty
        INNER JOIN dbo.Property Property
            ON InventoryProperty.PropertyId = Property.PropertyId
        WHERE InventoryProperty.InventoryId = @intInventoryId

    OPEN @CrsrVar

    FETCH NEXT FROM @CrsrVar
    INTO @chvProperty, @chvValue, @chvUnit

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @chvUnit = COALESCE(@chvUnit, '')
        -- Xg쐬
        SET @chvProperties = @chvProperties + @chvProperty + '='
                            + @chvValue + ' ' +  @chvUnit + '; '
        FETCH NEXT FROM @CrsrVar
        INTO @chvProperty, @chvValue, @chvUnit
    END

    CLOSE @CrsrVar
    DEALLOCATE @CrsrVar
    RETURN 0


CREATE PROCEDURE util.ap_Tables_BcpOut
AS
    DECLARE @chvTable VARCHAR(128),
            @chvCommand VARCHAR(255)

    DECLARE @curTables CURSOR

    -- Jgf[^x[X炷ׂẴ[U[`e[u擾
    SET @curTables = CURSOR FOR
        SELECT name
        FROM sysobjects
        WHERE xType = 'U'

    OPEN @curTables

    -- ŏ̃e[u擾
    FETCH NEXT FROM @curTables
    INTO @chvTable

    -- JgR[h̓ǂݎɐȀs
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        -- e[uGNX|[g邽߂DOSR}h𐶐
        SET @chvCommand = 'bcp Asset5.dbo.' + @chvTable
                        + ' out C:\backup\' + @chvTable
                        + '.txt -c -q -Slg -Usa -Pdejan'

        -- R}hC[eBeBsAe[uGNX|[g
        EXECUTE master.dbo.xp_cmdshell @chvCommand, NO_OUTPUT

        FETCH NEXT FROM @curTables
        INTO @chvTable
    END

    CLOSE @curTables
    DEALLOCATE @curTables
    RETURN 0

