---------------------------------------------------------------------
-- SQL Server 2005ストアドプロシージャプログラミング
-- 第4章 関数
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 4.1 関数の使用方法
---------------------------------------------------------------------

SELECT SIN(45)


SELECT @chvMake = Make,
       @Model = Model,
       @dtsCurrentDate = GETDATE()
FROM dbo.Equipment
WHERE EqID = @intEqId



CREATE PROCEDURE dbo.ap_Schedule_Insert
    @intScheduleId INT,
    @intLeaseId INT,
    @intLeaseFrequencyId INT
AS
    INSERT dbo.LeaseSchedule
    (
        ScheduleId,
        LeaseId,
        StartDate,
        EndDate,
        LeaseFrequencyId
    )
    VALUES
    (
        @intScheduleId,
        @intLeaseId,
        GETDATE(),
        DATEADD(year, 3, GETDATE()),
        @intLeaseFrequencyId
    )
    RETURN


SELECT Inventory.InventoryId
FROM LeaseSchedule
INNER JOIN Inventory
    ON LeaseSchedule.ScheduleId = Inventory.LeaseScheduleId
WHERE (LeaseSchedule.EndDate < GETDATE())
AND (Inventory.Rent <> 0)


CREATE TABLE [Order](
    OrderId INT IDENTITY(1, 1) NOT NULL,
    OrderDate SMALLDATETIME NOT NULL,
    RequestedById INT NOT NULL,
    TargetDate SMALLDATETIME NOT NULL,
    CompletionDate SMALLDATETIME NULL,
    DestinationLocationId INT NULL
) ON [PRIMARY]
GO

ALTER TABLE [Order] WITH NOCHECK ADD
    CONSTRAINT DF_Order_OrderDate DEFAULT(GETDATE()) FOR OrderDate,
    CONSTRAINT PK_Order PRIMARY KEY CLUSTERED(OrderId) ON [PRIMARY]
GO


DECLARE @dtmLastMonth DATETIME
SET @dtmLastMonth = DATEADD(month, -1, GETDATE())
SELECT *
FROM dbo.fnNewEquipment(@dtmLastMonth) NewEq
INNER JOIN dbo.EqType EqType
    ON NewEq.EqTypeId = EqType.EqTypeId


---------------------------------------------------------------------
-- 4.2 関数の種類
---------------------------------------------------------------------

CREATE PROCEDURE dbo.ap_LeasePeriodDuration_Get
-- リース期間のおおよその日数を返す
    @inyLeaseFrequencyId TINYINT,
    @insDays SMALLINT OUTPUT
AS
    DECLARE @chvLeaseFrequency VARCHAR(50)

    SELECT @chvLeaseFrequency = LeaseFrequency
    FROM dbo.LeaseFrequency
    WHERE LeaseFrequencyId = @inyLeaseFrequencyId
    SELECT @insDays =
        CASE @chvLeaseFrequency
            WHEN 'monthly' THEN 30
            WHEN 'semi-monthly' THEN 15
            WHEN 'bi-weekly' THEN 14
            WHEN 'weekly' THEN 7
            WHEN 'quarterly' THEN 92
            WHEN 'yearly' THEN 365
        END
    RETURN


CREATE PROCEDURE dbo.ap_LeaseInfo_List
-- リース契約情報をすべて表示する
AS
    SELECT LeaseVendor [Lease Vendor],
           LeaseNumber [Lease Number],
           CASE -- ベンダによっては、リース番号に販売員IDを含めていることがある
               WHEN LeaseVendor Like 'Trigon%'
                   THEN SUBSTRING(LeaseNumber, 5, 12)
               WHEN LeaseVendor Like 'S%'
                   THEN SUBSTRING(LeaseNumber, 9, 8)
               WHEN LeaseVendor Like 'Blue%'
                   THEN SUBSTRING(LeaseNumber, 7, 6)
               WHEN LeaseVendor = 'Diamond Financials'
                   THEN SUBSTRING(LeaseNumber, 8, 11)
               ELSE 'Unknown'
           END [Lease Agent],
           ContractDate [Contract Date]
    FROM dbo.Lease


CREATE PROCEDURE dbo.ap_LeaseContract_Load
    @intLeaseID INT,
    @chvLeaseVendor VARCHAR(50),
    @chvLeaseNumber VARCHAR(50),
    @chvLeaseDate VARCHAR(50)
AS
    DECLARE @intError INT

    -- 日付の妥当性を検査する
    IF ISDATE(@chvLeaseDate) = 0
    BEGIN
        RAISERROR('日付に変換できません。', 16, 1)
        RETURN -1
    END

    INSERT INTO dbo.Lease(LeaseID, LeaseVendor, LeaseNumber, ContractDate)
    VALUES(@intLeaseID, @chvLeaseVendor, @chvLeaseNumber,
        CONVERT(SMALLDATETIME, @chvLeaseDate))

    SELECT @intError = @@ERROR
    RETURN @intError


IF DATALENGTH(@Select) + DATALENGTH(@From) + DATALENGTH(@Where) <= 8000
    SELECT @SQL = @Select + @From + @Where


CREATE PROCEDURE ap_Equipment_Update
-- レコードが変更されている場合にのみ
-- Equipmentテーブルを更新する
    @intEqId INT,
    @chvMake VARCHAR(50),
    @chvModel VARCHAR(50),
    @intEqTypeId INT
AS
    DECLARE @intNewEqBC INT
    SET @intNewEqBC = BINARY_CHECKSUM(@chvMake, @chvModel, @intEqTypeId)

    -- レコードが存在するかどうかをチェックする
    IF NOT EXISTS (SELECT *
                   FROM Equipment
                   WHERE EqId = @intEqId)
    BEGIN
        RAISERROR('Equipmentテーブルに該当するレコードが存在しません。', 16, 1)
        RETURN -1
    END

    -- 当該レコードのチェックサムがEquipmentBCに存在しない場合は、ここで作成する
    IF NOT EXISTS (SELECT EqBC
                   FROM EquipmentBC
                   WHERE EqId = @intEqId)
        INSERT EquipmentBC(EqId, EqBC)
            SELECT @intEqId, BINARY_CHECKSUM(Make, Model, EqTypeId)
            FROM Equipment
            WHERE EqId = @intEqId

    -- チェックサムが異なる場合にのみ、Equipmentテーブルを更新する
    IF @intNewEqBC <> (SELECT EqBC
                       FROM EquipmentBC
                       WHERE EqId = @intEqId)
    BEGIN
        UPDATE Equipment
        SET Make = @chvMake,
            Model = @chvModel,
            EqTypeId = @intEqTypeId
        WHERE EqId = @intEqId

        UPDATE EquipmentBC
        SET EqBC = @intNewEqBC
        WHERE EqId = @intEqId
    END
    RETURN


SELECT AVG(Lease) [NULLを除外した平均値],
       AVG(NULLIF(Lease, 0)) [NULLと0の両方を除外した平均値]
FROM Inventory


SELECT AVG(Lease) [NULL値を除外して計算],
       AVG(ISNULL(Lease, 0)) [NULL値を0に変えて計算]
FROM Inventory


SELECT Inventory.Inventoryid,
       Equipment.Make + ' ' + Equipment.Model Equipment,
       AcquisitionType.AcquisitionType,
       COALESCE(Inventory.Rent, Inventory.Lease, Inventory.Cost) [Cost]
FROM Inventory
INNER JOIN AcquisitionType
    ON Inventory.AcquisitionTypeID = AcquisitionType.AcquisitionTypeId
INNER JOIN Equipment
    ON Inventory.EqId = Equipment.EqId
ORDER BY Inventory.InventoryId


SELECT N'エラー [' + CAST(@@ERROR AS NVARCHAR(8)) + N'] が発生しました。'


SELECT GETDATE() standard, CONVERT(VARCHAR, GETDATE(), 104) German


SELECT $12345678.90,
       CONVERT(VARCHAR(30), $12345678.90, 0),
       CONVERT(VARCHAR(30), $12345678.90, 1),
       CONVERT(VARCHAR(30), $12345678.90, 2)


SELECT $12345678.90,
       CONVERT(VARCHAR, $12345678.90, 0),
       CONVERT(VARCHAR, $12345678.90, 1),
       CONVERT(VARCHAR, $12345678.90, 2)


CREATE PROCEDURE dbo.ap_Order_Add
    @dtmOrderDate DATETIME = NULL,
    @dtmTargetDate DATETIME = NULL,
    @chvUserName VARCHAR(128) = NULL,
    @intDestinationLocation INT,
    @intOrderid INT OUTPUT
AS
    DECLARE @intRequestedById INT

    -- 注文日を指定しなかったときは、既定で今日とする
    IF @dtmOrderDate IS NULL
        SET @dtmOrderDate = GETDATE()

    -- 目標日を指定しなかったときは、既定で注文日の3日後とする
    IF @dtmTargetDate IS NULL
        SET @dtmTargetDate = DATEADD(day, 3, @dtmOrderDate)

    -- ユーザーが名乗らなかったときは、ログイン名から人物を特定する
    IF @chvUserName IS NULL
        ★SET @chvUserName = SYSTEM_USER☆

    -- ユーザーのIDを取得する
    SELECT @intRequestedById = ContactId
    FROM dbo.Contact
    WHERE UserName = @chvUserName

    -- ユーザーを特定できないときは、エラーを報告する
    IF @intRequestedById IS NULL
    BEGIN
        RAISERROR('Contactテーブルにユーザー名がありません', 1, 2)
        RETURN -1
    END

    -- ここで注文を挿入する
    INSERT INTO [Order](OrderDate, RequestedById, TargetDate, DestinationLocationId)
    VALUES(@dtmOrderDate, @intRequestedById, @dtmTargetDate, @intDestinationLocation)

    SET @intOrderid = SCOPE_IDENTITY()
    RETURN


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


DECLARE @intEqId int
INSERT INTO Equipment(Make, Model, EqTypeId)
VALUES ('ACME', 'Turbo', 2)
SELECT @intEqId = SCOPE_IDENTITY()
SELECT @intEqId [EqId]


SELECT IDENT_SEED('Inventory'), IDENT_INCR('Inventory')


SELECT IDENTITY(INT, 1, 1) AS ID,
       Property.Property,
       InventoryProperty.Value,
       Property.Unit
INTO #InventoryProperty
FROM dbo.InventoryProperty InventoryProperty
INNER JOIN dbo.Property Property
    ON InventoryProperty.PropertyId = Property.PropertyId
WHERE (InventoryProperty.InventoryId = 12)


SELECT IDENT_CURRENT('Equipment')


SELECT GETDATE() AS 'Date',
       DATENAME(weekday, GETDATE()) AS 'Day of the Week',
       DATENAME(month, GETDATE()) AS 'Month Name',
       DATEPART(yyyy, GETDATE()) AS 'Year'


ALTER PROCEDURE ap_Terms_List
    @dtsStartDate SMALLDATETIME,
    @dtsEndDate SMALLDATETIME,
    @chvLeaseFrequency VARCHAR(20)
AS
SET NOCOUNT ON
DECLARE @insDueDates SMALLINT -- リース満了日の総数
-- リース満了日の数を計算する
SELECT @insDueDates =
    CASE @chvLeaseFrequency
         WHEN 'monthly'
              THEN DATEDIFF(month, @dtsStartDate, @dtsEndDate)
         WHEN 'semi-monthly'
              THEN 2*DATEDIFF(month, @dtsStartDate, @dtsEndDate)
         WHEN 'bi-weekly'
              THEN DATEDIFF(week, @dtsStartDate, @dtsEndDate)/2
         WHEN 'weekly'
              THEN DATEDIFF(week, @dtsStartDate, @dtsEndDate)
         WHEN 'quarterly'
              THEN DATEDIFF(qq, @dtsStartDate, @dtsEndDate)
         WHEN 'yearly'
              THEN DATEDIFF(y, @dtsStartDate, @dtsEndDate)
     END

-- 一時テーブルを使ってリース満了日の一覧を生成する
CREATE TABLE #DueDates(ID INT)

WHILE @insDueDates >= 0
BEGIN
    INSERT #DueDates(ID) VALUES(@insDueDates)
    SELECT @insDueDates = @insDueDates - 1
END

-- リース満了日の一覧を表示する
SELECT ID+1,
       CONVERT
       (
           VARCHAR,
           CASE
               WHEN @chvLeaseFrequency = 'monthly'
                  THEN DATEADD(month, ID, @dtsStartDate)
               WHEN @chvLeaseFrequency = 'semi-monthly'
               AND ID/2 = CAST(ID AS FLOAT)/2
                   THEN DATEADD(month, ID/2, @dtsStartDate)
               WHEN @chvLeaseFrequency = 'semi-monthly'
               AND ID/2 <> CAST(ID AS FLOAT)/2
                   THEN DATEADD(dd, 15, DATEADD(month, ID/2, @dtsStartDate))
               WHEN @chvLeaseFrequency = 'bi-weekly'
                   THEN DATEADD(week, ID*2, @dtsStartDate)
               WHEN @chvLeaseFrequency = 'weekly'
                   THEN DATEADD(week, ID, @dtsStartDate)
               WHEN @chvLeaseFrequency = 'quarterly'
                   THEN DATEADD(qq, ID, @dtsStartDate)
               WHEN @chvLeaseFrequency = 'yearly'
                   THEN DATEADD(y, ID, @dtsStartDate)
           END,
           105
       ) [Due date]
FROM #DueDates
ORDER BY ID
-- 後片付けをする
DROP TABLE #DueDates
RETURN


DECLARE @chvEquipment NVARCHAR(30)
SET @chvEquipment = N'東芝 Portege'
SELECT LEN(@chvEquipment),
       DATALENGTH(@chvEquipment)


DECLARE @chvEquipment NVARCHAR(30)
SET @chvEquipment = N'東芝 Portege'
SELECT LEFT(@chvEquipment, 2) Make, Right(@chvEquipment, 7) Model


DECLARE @chvEquipment NVARCHAR(30)
SET @chvEquipment = N'東芝 Portege'
SELECT SUBSTRING(@chvEquipment, 4, 7)


CREATE PROCEDURE ap_SplitFullName
    @chvFullName NVARCHAR(50),
    @chvDelimiter NVARCHAR(3) = ', ',
    @chvFirstName NVARCHAR(50) OUTPUT,
    @chvLastName NVARCHAR(50) OUTPUT
AS
    SET NOCOUNT ON

    DECLARE @intPosition INT
    SET @intPosition = CHARINDEX(@chvDelimiter, @chvFullName)

    IF @intPosition > 0
    BEGIN
        SET @chvLastName = LEFT(@chvFullName, @intPosition - 1)
        SET @chvFirstName = RIGHT(@chvFullName,
            LEN(@chvFullName) - (@intPosition - 1 + DATALENGTH(@chvDelimiter)/2))
    END
    ELSE
        RETURN 1
    RETURN 0


SET @intPosition = PATINDEX('%,%', @chvFullName)


SELECT LTRIM(RTRIM('    Dejan Sunderic   '))


SELECT NCHAR(352) + 'underi' + NCHAR(263)


SELECT SPACE(4) + REPLICATE('*', 8)


SELECT STUFF('Sunderic, Dejan', 9, 2, CHAR(9))


DECLARE @Lookup SQL_VARIANT

SET @Lookup = 123.4567

SELECT SQL_VARIANT_PROPERTY(@Lookup, 'BaseType') 'BaseType',
       SQL_VARIANT_PROPERTY(@Lookup, 'Precision') 'Precision',
       SQL_VARIANT_PROPERTY(@Lookup, 'Scale') 'Scale'


SELECT AVG(Rent) [Average Rent] FROM Inventory


SELECT COUNT(Rent) [Rentals] FROM Inventory


SELECT COUNT(*) [Assets] FROM Inventory


SELECT locationId, COUNT(*) InvCount
FROM Inventory
GROUP BY LocationId
ORDER BY InvCount


SELECT LocationId,
       InvCount,
       ROW_NUMBER() OVER(ORDER BY InvCount) Row_Number
FROM
(
    SELECT locationId, COUNT(*) InvCount
    FROM Inventory
    GROUP BY LocationId
) InvCount


SELECT LocationId,
       InvCount,
       ROW_NUMBER() OVER(ORDER BY InvCount) Row_Number,
       RANK() OVER(ORDER BY InvCount) Rank,
       DENSE_RANK() OVER(ORDER BY InvCount) Dense_Rank
FROM
(
    SELECT locationId, COUNT(*) InvCount
    FROM Inventory
    GROUP BY LocationId
) InvCount


EXEC sp_addlinkedserver
    @server = 'Northwind_Access',
    @provider = 'Microsoft.Jet.OLEDB.4.0',
    @srvproduct = 'OLE DB Provider for Jet',
    @datasrc = 'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb'
GO


SELECT *
FROM OPENQUERY(Northwind_Access, 'SELECT * FROM 社員')


EXEC sp_addlinkedserver
    @server = N'RemoteSrv1',
    @srvproduct = N'SQL Server'
GO

SELECT * FROM RemoteSrv1.Asset5.dbo.Inventory


SELECT a.au_lname, a.au_fname, titles.title
FROM OPENROWSET
(
    'SQLNCLI',
    'Server=RemoteSrv1;UID=usrremote01;PWD=password;',
    'SELECT * FROM pubs.dbo.authors'
) AS a
INNER JOIN titleauthor
    ON a.au_id = titleauthor.au_id
INNER JOIN titles
    ON titleauthor.title_id = titles.title_id


DECLARE @intDoc INT
DECLARE @chvXMLDoc VARCHAR(max)
-- サンプルXMLドキュメント
SET @chvXMLDoc ='
<root>
  <Equipment EquipmentID="1" Make="Toshiba" Model="Portege 7020CT">
    <Inventory InventoryID="5" StatusID="1" EquipmentID="1"/>
    <Inventory InventoryID="12" StatusID="1" EquipmentID="1"/>
  </Equipment>
</root>'
-- XMLドキュメントをメモリに読み込む
EXEC sp_xml_preparedocument @intDoc OUTPUT, @chvXMLDoc


DECLARE @intDoc INT
DECLARE @chvXMLDoc VARCHAR(max)
-- サンプルXMLドキュメント
SET @chvXMLDoc =
'<root>
  <Equipment EquipmentID="1" Make="Toshiba" Model="Portege 7020CT">
    <Inventory InventoryID="5" StatusID="1" EquipmentID="1"/>
    <Inventory InventoryID="12" StatusID="1" EquipmentID="1"/>
  </Equipment>
  <Equipment EquipmentID="2" Make="Sony" Model="Trinitron 17XE"/>
  <Equipment EquipmentID="4" Make="HP" Model="LaserJet 4"/>
  <Equipment EquipmentID="5" Make="Bang &amp; Olafson" Model="V4000">
    <Inventory InventoryID="8" StatusID="1" EquipmentID="5"/>
  </Equipment>
  <Equipment EquipmentID="6" Make="NEC" Model="V90">
    <Inventory InventoryID="6" StatusID="2" EquipmentID="6"/>
  </Equipment>
</root>'
-- XMLドキュメントをメモリに読み込む
EXEC sp_xml_preparedocument @intDoc OUTPUT, @chvXMLDoc
-- OPENXML関数を使ったSELECTステートメント
SELECT *
FROM OPENXML(@intDoc, '/root/Equipment/Inventory', 8)
     WITH
     (
         InventoryID INT '@InventoryID',
         StatusID INT '@StatusID',
         Make VARCHAR(25) '../@Make',
         Model VARCHAR(25) '../@Model',
         Comment NTEXT '@mp:xmltext'
     )
-- XMLドキュメントをメモリから削除する
EXEC sp_xml_removedocument @intDoc


SELECT *
FROM OPENXML(@intDoc, '/root/Equipment', 2)
     WITH
    (
        EquipmentID INT '@EquipmentID',
        Branch ntext '@mp:xmltext'
    )
WHERE EquipmentID = 1

