---------------------------------------------------------------------
-- SQL Server 2005ストアドプロシージャプログラミング
-- 第2章 ストアドプロシージャの設計
---------------------------------------------------------------------

---------------------------------------------------------------------
-- 2.1 ストアドプロシージャの解剖
---------------------------------------------------------------------

CREATE PROCEDURE ap_Equipment_Get @chvMake VARCHAR(50)
AS
    SELECT * FROM dbo.Equipment
    WHERE Make = @chvMake


DROP PROCEDURE ap_EquipmentByEqTypeID_Get
GO
CREATE PROCEDURE ap_EquipmentByEqTypeID_Get @intEqTypeId INT
AS
    SELECT * FROM dbo.Equipment
    WHERE EqTypeId = @intEqTypeId
GO


IF EXISTS
(
    SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'ap_EquipmentByEqTypeID_Get')
    AND type IN(N'P', N'PC')
)
DROP PROCEDURE ap_EquipmentByEqTypeID_Get
GO

CREATE PROCEDURE ap_EquipmentByEqTypeID_Get @intEqTypeId INT
AS
    SELECT * FROM dbo.Equipment
    WHERE EqTypeId = @intEqTypeId
GO


ALTER PROCEDURE ap_Equipment_Get @chvMake VARCHAR(50)
AS
    SELECT * FROM dbo.Equipment
    WHERE Make = @chvMake
GO


CREATE PROCEDURE dbo.ap_EqIdByMakeModel_List
    @chvMake VARCHAR(50),
    @chvModel VARCHAR(50)
AS
    SELECT EqId
    FROM dbo.Equipment
    WHERE Make = @chvMake
    AND Model = @chvModel
GO


EXECUTE dbo.ap_EqIdByMakeModel_List 'Toshiba', 'Portege 7020CT'


dbo.ap_EqIdByMakeModel_List 'Toshiba', 'Portege 7020CT'


EXEC dbo.ap_EqIdByMakeModel_List 'Toshiba', 'Portege 7020CT'


CREATE PROCEDURE dbo.ap_EqIdByMakeModel_List_2
    @chvMake VARCHAR(50),
    @chvModel VARCHAR(50),
    @intEqId INT OUTPUT
AS
    SELECT @intEqId = EqId
    FROM dbo.Equipment
    WHERE Make = @chvMake
    AND Model = @chvModel


DECLARE @EqId INT
EXECUTE dbo.ap_EqIdByMakeModel_List_2 'Toshiba',
                                      'Portege 7020CT',
                                      @EqId OUTPUT
SELECT @EqId 'Equipment Identifier'


CREATE PROCEDURE ap_EqIdByMakeModel_List_3
    @chvMake VARCHAR(50),
    @chvModel VARCHAR(50)
AS
    DECLARE @intEqId INT
    SELECT @intEqId = EqId
    FROM dbo.Equipment
    WHERE Make = @chvMake
    AND Model = @chvModel
    RETURN @intEqId


CREATE PROCEDURE dbo.ap_EqIdByMakeModel_List_4
    @chvMake VARCHAR(50),
    @chvModel VARCHAR(50)
AS
    RETURN
    (
        SELECT EqId
        FROM dbo.Equipment
        WHERE Make = @chvMake
        AND Model = @chvModel
    )


DECLARE @intEqId INT
EXECUTE @intEqId = ap_EqIdByMakeModel_List_3 'Toshiba', 'Portege 7020CT'
SELECT @intEqId 'Equipment Identifier'


CREATE PROCEDURE dbo.ap_EqIdByMakeModel_List_5
    @chvMake VARCHAR(50),
    @chvModel VARCHAR(50),
    @intEqId INT OUTPUT
AS
    SELECT @intEqId = EqId
    FROM dbo.Equipment
    WHERE Make = @chvMake
    AND Model = @chvModel
    RETURN @@ERROR


DECLARE @intEqId INT, @intErrorCode INT
EXECUTE @intErrorCode = dbo.ap_EqIdByMakeModel_List_5
    'Toshiba',
    'Portege 7020CT',
    @intEqId OUTPUT
SELECT @intEqId result, @intErrorCode ErrorCode


CREATE PROCEDURE dbo.ap_EqIdByMakeModel_List_6
    @chvMake  VARCHAR(50) = '%',
    @chvModel VARCHAR(50) = '%'
AS
    SELECT * FROM dbo.Equipment
    WHERE Make Like @chvMake
    AND Model Like @chvModel


EXECUTE dbo.ap_EqIdByMakeModel_List_6 'T%', 'Portege%'


EXECUTE dbo.ap_EqIdByMakeModel_List_6 'T%'


EXECUTE dbo.ap_EqIdByMakeModel_List_6


EXECUTE dbo.ap_EqIdByMakeModel_List_6 @chvModel = 'T%'


---------------------------------------------------------------------
-- 2.3 ストアドプロシージャの管理
---------------------------------------------------------------------

EXEC sp_stored_procedures


SELECT * FROM sys.objects


SELECT * FROM sys.objects
WHERE type = 'P'


USE Asset5
EXEC sp_helptext 'dbo.ap_EqIdByMakeModel_List'


CREATE PROC Procedure2
AS
    SELECT 'Hello world'
    SELECT * FROM dbo.EqType
GO

EXEC sp_rename 'Procedure2', 'Procedure_2'


SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID(N'Asset5.dbo.Procedure_2')


DROP PROCEDURE dbo.Procedure_1


EXEC sp_depends vEquipmentFull

