Merhabalar ,
Gectigimiz Günlerde Forumda Paylasildigini Gördüm Bir İhtimal Yapamayanlarimiz Olmustur Diye Düsünerek Prosedürü Direk Düzeltilmiş Halde Veriyorum . Bu Prosedür İle Her Database'de Bulunan Rental İtem NPC'sini Aktif Hale Getirebilirsiniz. Bazi Database'ler de İtem Register Ederken Hata Verir Bu Prosedürü Uyguladiktan Sonra Yapmaniz Gereken 1 Adet Rental Scroll Kiraya Vereceginiz İtem ve Rental NPC'si
Bir Teşekkür Yeterli
Kod:
/****** Object: Stored Procedure dbo.RENTAL_ITEM_REGISTER Script Date: 6/6/2006 6:03:34 PM ******/
CREATE PROCEDURE RENTAL_ITEM_REGISTER
@charID char(21),
@AccountID char(21),
@sRentalTime smallint,
@nItemID int,
@sDurability smallint,
@nMoney int,
@bGameBangType tinyint,
@bItemType tinyint,
@bItemClass tinyint,
@nSerialNumber bigint,
@nRet_Index int OUTPUT,
@nRet smallint OUTPUT
AS
DECLARE @Row tinyint, @nRentalIndex int
DECLARE @timeRegister smalldatetime
SET @Row = 0
SET @nRet_Index = -1
SET @timeRegister = getdate()
---------------------------------------
--SET @nRet = -9
--RETURN
SELECT @Row = COUNT(*) FROM RENTAL_ITEM WHERE nItemIndex = @nItemID and strLenderAcID = @AccountID
IF @Row > 0
BEGIN
SET @nRet = -9
RETURN
END
BEGIN TRAN
INSERT INTO
RENTAL_ITEM ( nItemIndex, sDurability, nSerialNumber, byRegType, byItemType, byClass, sRentalTime, nRentalMoney, strLenderCharID, strLenderAcID )
VALUES (@nItemID, @sDurability, @nSerialNumber, @bGameBangType, @bItemType, @bItemClass, @sRentalTime, @nMoney, @charID, @AccountID )
IF @@ERROR 0
BEGIN
ROLLBACK TRAN
SET @nRet = -16
RETURN
END
SELECT @nRentalIndex = nRentalIndex FROM RENTAL_ITEM WHERE nItemIndex = @nItemID and strLenderAcID = @AccountID
-- 임대자정보에 추가(등록)
INSERT INTO
USER_RENTAL_ITEM ( strUserID, strAccountID, byRentalType, byRegType, nRentalIndex, nItemIndex, sDurability, nSerialNumber, nRentalMoney, sRentalTime, sDuringTime, timeRegister )
VALUES ( @charID, @AccountID, 1, @bGameBangType, @nRentalIndex, @nItemID, @sDurability, @nSerialNumber, @nMoney, @sRentalTime, @sRentalTime, @timeRegister )
IF @@ERROR 0
BEGIN
ROLLBACK TRAN
SET @nRet = -17
RETURN
END
-- SELECT @nRentalIndex = nRentalIndex FROM RENTAL_ITEM WHERE nItemIndex = @nItemID and strLenderAcID = @AccountID
SET @nRet_Index = @nRentalIndex
COMMIT TRAN
SET @nRet = 1
GO
2. Prosedür
Kod:
/****** Object: Stored Procedure dbo.RENTAL_ITEM_CANCEL Script Date: 6/6/2006 6:03:33 PM ******/
-- 2004.03.26. sungyong
CREATE PROCEDURE RENTAL_ITEM_CANCEL
@AccountID char(21), -- 등록자 계정
@CharID char(21), -- 등록자 캐릭 아이디
@nRentalIndex int,
@nItemNumber int,
@nRet smallint OUTPUT
AS
--SET @Row = 0
-- rentallist 삭제
DELETE FROM RENTAL_ITEM WHERE nRentalIndex = @nRentalIndex and nItemIndex = @nItemNumber
-- 임대자정보에 대여정보 삭제
DELETE FROM USER_RENTAL_ITEM WHERE nRentalIndex = @nRentalIndex and nItemIndex = @nItemNumber
SET @nRet = 1
GO
3.prosedür
Kod:
/****** Object: Stored Procedure dbo.RENTAL_ITEM_DESTORY Script Date: 6/6/2006 6:03:33 PM ******/
-- 2004.03.26. sungyong
CREATE PROCEDURE RENTAL_ITEM_DESTORY
@AccountID char(21),
@CharID char(21),
@nItemNumber int,
@nRentalIndex int,
@nDurability smallint,
@nRet smallint OUTPUT
AS
DELETE FROM USER_RENTAL_ITEM WHERE strAccountID = @AccountID and nItemIndex = @nItemNumber
EXEC RENTAL_ITEM_DURABILITY_UPDATE @nRentalIndex, @nDurability
SET @nRet = 1
GO
4.prosedür
Kod:
/****** Object: Stored Procedure dbo.RENTAL_ITEM_DURABILITY_UPDATE Script Date: 6/6/2006 6:03:34 PM ******/
-- 2004.04.22. sungyong
CREATE PROCEDURE RENTAL_ITEM_DURABILITY_UPDATE
@nRentalIndex int,
@nDurability int
AS
DECLARE @Row tinyint
DECLARE @LendercharID char(21), @LenderAccountID char(21)
SET @Row = 0
SELECT @LendercharID = strLenderCharID, @LenderAccountID = strLenderAcID
FROM RENTAL_ITEM_LIST
WHERE nRentalIndex = @nRentalIndex
-- 임대자정보에 내구력 업데이트
UPDATE USER_RENTAL_ITEM
SET sDurability = @nDurability
WHERE strAccountID = @LenderAccountID and nRentalIndex = @nRentalIndex
GO
5.prosedür
Kod:
/****** Object: Stored Procedure dbo.RENTAL_ITEM_LEND Script Date: 6/6/2006 6:03:34 PM ******/
-- 2004.03.26. sungyong
CREATE PROCEDURE RENTAL_ITEM_LEND
@AccountID char(21), -- 대여자 계정
@CharID char(21), -- 대여자 캐릭 아이디
@nRentalIndex int,
@nItemNumber int,
@nRet smallint OUTPUT
AS
SET @nRet = -5
RETURN
DECLARE @Row tinyint, @sDurability smallint, @nSerialNumber bigint
DECLARE @bItemType tinyint, @bItemClass tinyint, @sRentalTime smallint, @nMoney int, @LendercharID char(21), @LenderAccountID char(21)
DECLARE @timeLend smalldatetime, @timeRegister smalldatetime
SET @Row = 0
SET @timeLend = getdate()
SELECT @sDurability=sDurability, @nSerialNumber=nSerialNumber, @bItemType=byItemType, @bItemClass=byClass,
@sRentalTime=sRentalTime, @nMoney=nRentalMoney, @LendercharID=strLenderCharID, @LenderAccountID=strLenderAcID, @timeRegister=timeRegister
FROM RENTAL_ITEM
WHERE nRentalIndex = @nRentalIndex and nItemIndex = @nItemNumber
IF @bItemType = 0
BEGIN
SET @nRet = -5
RETURN
END
-- list에 입력
INSERT INTO
RENTAL_ITEM_LIST ( nRentalIndex, nItemIndex, sDurability, nSerialNumber, byItemType, byClass, sRentalTime, nRentalMoney, strLenderCharID, strLenderAcID, strBorrowerCharID, strBorrowerAcID, timeLender, timeRegister )
VALUES (@nRentalIndex, @nItemNumber, @sDurability, @nSerialNumber, @bItemType, @bItemClass, @sRentalTime, @nMoney, @LendercharID, @LenderAccountID, @CharID, @AccountID, @timeLend, @timeLend )
-- rentallist 삭제
DELETE FROM RENTAL_ITEM WHERE nRentalIndex = @nRentalIndex and nItemIndex = @nItemNumber
-- 대여자정보에 추가
INSERT INTO
USER_RENTAL_ITEM ( strUserID, strAccountID, byRentalType, nRentalIndex, nItemIndex, sDurability, nSerialNumber, nRentalMoney, sRentalTime, sDuringTime, timeRental, timeRegister )
VALUES ( @CharID, @AccountID, 3, @nRentalIndex, @nItemNumber, @sDurability, @nSerialNumber, 0, @sRentalTime, @sRentalTime, @timeLend, @timeRegister )
-- 임대자정보에 대여정보 추가
UPDATE USER_RENTAL_ITEM
SET byRentalType=2, nRentalMoney = @nMoney, timeRental = @timeLend
WHERE strAccountID = @LenderAccountID and nRentalIndex = @nRentalIndex
SET @nRet = 1
GO
NOT : Diger Prosedürler 1. Yani Ana Prosedürü Uyguladıkları Halde Calistiramayanlar İcin Verilmiştir . İsteyenler Komple Bunları Bastan Ekleyebilir
Kullanımı ...
1 Başlat > Tüm Programlar > Microsoft SQL Server > Query Analyzer ‘ a giriyoruz.
2 SQL Server’ a bağlanıyoruz ve açılan ekranda ” master ” yerine ” DBmizi ” seçiyoruz.
3 Seçtikten sonra aşağıdaki kodu kopyalayıp , yapıştırıyoruz ve F5 yaparak kodumuzu uyguluyoruz.
Gectigimiz Günlerde Forumda Paylasildigini Gördüm Bir İhtimal Yapamayanlarimiz Olmustur Diye Düsünerek Prosedürü Direk Düzeltilmiş Halde Veriyorum . Bu Prosedür İle Her Database'de Bulunan Rental İtem NPC'sini Aktif Hale Getirebilirsiniz. Bazi Database'ler de İtem Register Ederken Hata Verir Bu Prosedürü Uyguladiktan Sonra Yapmaniz Gereken 1 Adet Rental Scroll Kiraya Vereceginiz İtem ve Rental NPC'si
Kod:
/****** Object: Stored Procedure dbo.RENTAL_ITEM_REGISTER Script Date: 6/6/2006 6:03:34 PM ******/
CREATE PROCEDURE RENTAL_ITEM_REGISTER
@charID char(21),
@AccountID char(21),
@sRentalTime smallint,
@nItemID int,
@sDurability smallint,
@nMoney int,
@bGameBangType tinyint,
@bItemType tinyint,
@bItemClass tinyint,
@nSerialNumber bigint,
@nRet_Index int OUTPUT,
@nRet smallint OUTPUT
AS
DECLARE @Row tinyint, @nRentalIndex int
DECLARE @timeRegister smalldatetime
SET @Row = 0
SET @nRet_Index = -1
SET @timeRegister = getdate()
---------------------------------------
--SET @nRet = -9
--RETURN
SELECT @Row = COUNT(*) FROM RENTAL_ITEM WHERE nItemIndex = @nItemID and strLenderAcID = @AccountID
IF @Row > 0
BEGIN
SET @nRet = -9
RETURN
END
BEGIN TRAN
INSERT INTO
RENTAL_ITEM ( nItemIndex, sDurability, nSerialNumber, byRegType, byItemType, byClass, sRentalTime, nRentalMoney, strLenderCharID, strLenderAcID )
VALUES (@nItemID, @sDurability, @nSerialNumber, @bGameBangType, @bItemType, @bItemClass, @sRentalTime, @nMoney, @charID, @AccountID )
IF @@ERROR 0
BEGIN
ROLLBACK TRAN
SET @nRet = -16
RETURN
END
SELECT @nRentalIndex = nRentalIndex FROM RENTAL_ITEM WHERE nItemIndex = @nItemID and strLenderAcID = @AccountID
-- 임대자정보에 추가(등록)
INSERT INTO
USER_RENTAL_ITEM ( strUserID, strAccountID, byRentalType, byRegType, nRentalIndex, nItemIndex, sDurability, nSerialNumber, nRentalMoney, sRentalTime, sDuringTime, timeRegister )
VALUES ( @charID, @AccountID, 1, @bGameBangType, @nRentalIndex, @nItemID, @sDurability, @nSerialNumber, @nMoney, @sRentalTime, @sRentalTime, @timeRegister )
IF @@ERROR 0
BEGIN
ROLLBACK TRAN
SET @nRet = -17
RETURN
END
-- SELECT @nRentalIndex = nRentalIndex FROM RENTAL_ITEM WHERE nItemIndex = @nItemID and strLenderAcID = @AccountID
SET @nRet_Index = @nRentalIndex
COMMIT TRAN
SET @nRet = 1
GO
2. Prosedür
Kod:
/****** Object: Stored Procedure dbo.RENTAL_ITEM_CANCEL Script Date: 6/6/2006 6:03:33 PM ******/
-- 2004.03.26. sungyong
CREATE PROCEDURE RENTAL_ITEM_CANCEL
@AccountID char(21), -- 등록자 계정
@CharID char(21), -- 등록자 캐릭 아이디
@nRentalIndex int,
@nItemNumber int,
@nRet smallint OUTPUT
AS
--SET @Row = 0
-- rentallist 삭제
DELETE FROM RENTAL_ITEM WHERE nRentalIndex = @nRentalIndex and nItemIndex = @nItemNumber
-- 임대자정보에 대여정보 삭제
DELETE FROM USER_RENTAL_ITEM WHERE nRentalIndex = @nRentalIndex and nItemIndex = @nItemNumber
SET @nRet = 1
GO
3.prosedür
Kod:
/****** Object: Stored Procedure dbo.RENTAL_ITEM_DESTORY Script Date: 6/6/2006 6:03:33 PM ******/
-- 2004.03.26. sungyong
CREATE PROCEDURE RENTAL_ITEM_DESTORY
@AccountID char(21),
@CharID char(21),
@nItemNumber int,
@nRentalIndex int,
@nDurability smallint,
@nRet smallint OUTPUT
AS
DELETE FROM USER_RENTAL_ITEM WHERE strAccountID = @AccountID and nItemIndex = @nItemNumber
EXEC RENTAL_ITEM_DURABILITY_UPDATE @nRentalIndex, @nDurability
SET @nRet = 1
GO
4.prosedür
Kod:
/****** Object: Stored Procedure dbo.RENTAL_ITEM_DURABILITY_UPDATE Script Date: 6/6/2006 6:03:34 PM ******/
-- 2004.04.22. sungyong
CREATE PROCEDURE RENTAL_ITEM_DURABILITY_UPDATE
@nRentalIndex int,
@nDurability int
AS
DECLARE @Row tinyint
DECLARE @LendercharID char(21), @LenderAccountID char(21)
SET @Row = 0
SELECT @LendercharID = strLenderCharID, @LenderAccountID = strLenderAcID
FROM RENTAL_ITEM_LIST
WHERE nRentalIndex = @nRentalIndex
-- 임대자정보에 내구력 업데이트
UPDATE USER_RENTAL_ITEM
SET sDurability = @nDurability
WHERE strAccountID = @LenderAccountID and nRentalIndex = @nRentalIndex
GO
5.prosedür
Kod:
/****** Object: Stored Procedure dbo.RENTAL_ITEM_LEND Script Date: 6/6/2006 6:03:34 PM ******/
-- 2004.03.26. sungyong
CREATE PROCEDURE RENTAL_ITEM_LEND
@AccountID char(21), -- 대여자 계정
@CharID char(21), -- 대여자 캐릭 아이디
@nRentalIndex int,
@nItemNumber int,
@nRet smallint OUTPUT
AS
SET @nRet = -5
RETURN
DECLARE @Row tinyint, @sDurability smallint, @nSerialNumber bigint
DECLARE @bItemType tinyint, @bItemClass tinyint, @sRentalTime smallint, @nMoney int, @LendercharID char(21), @LenderAccountID char(21)
DECLARE @timeLend smalldatetime, @timeRegister smalldatetime
SET @Row = 0
SET @timeLend = getdate()
SELECT @sDurability=sDurability, @nSerialNumber=nSerialNumber, @bItemType=byItemType, @bItemClass=byClass,
@sRentalTime=sRentalTime, @nMoney=nRentalMoney, @LendercharID=strLenderCharID, @LenderAccountID=strLenderAcID, @timeRegister=timeRegister
FROM RENTAL_ITEM
WHERE nRentalIndex = @nRentalIndex and nItemIndex = @nItemNumber
IF @bItemType = 0
BEGIN
SET @nRet = -5
RETURN
END
-- list에 입력
INSERT INTO
RENTAL_ITEM_LIST ( nRentalIndex, nItemIndex, sDurability, nSerialNumber, byItemType, byClass, sRentalTime, nRentalMoney, strLenderCharID, strLenderAcID, strBorrowerCharID, strBorrowerAcID, timeLender, timeRegister )
VALUES (@nRentalIndex, @nItemNumber, @sDurability, @nSerialNumber, @bItemType, @bItemClass, @sRentalTime, @nMoney, @LendercharID, @LenderAccountID, @CharID, @AccountID, @timeLend, @timeLend )
-- rentallist 삭제
DELETE FROM RENTAL_ITEM WHERE nRentalIndex = @nRentalIndex and nItemIndex = @nItemNumber
-- 대여자정보에 추가
INSERT INTO
USER_RENTAL_ITEM ( strUserID, strAccountID, byRentalType, nRentalIndex, nItemIndex, sDurability, nSerialNumber, nRentalMoney, sRentalTime, sDuringTime, timeRental, timeRegister )
VALUES ( @CharID, @AccountID, 3, @nRentalIndex, @nItemNumber, @sDurability, @nSerialNumber, 0, @sRentalTime, @sRentalTime, @timeLend, @timeRegister )
-- 임대자정보에 대여정보 추가
UPDATE USER_RENTAL_ITEM
SET byRentalType=2, nRentalMoney = @nMoney, timeRental = @timeLend
WHERE strAccountID = @LenderAccountID and nRentalIndex = @nRentalIndex
SET @nRet = 1
GO
NOT : Diger Prosedürler 1. Yani Ana Prosedürü Uyguladıkları Halde Calistiramayanlar İcin Verilmiştir . İsteyenler Komple Bunları Bastan Ekleyebilir
Kullanımı ...
1 Başlat > Tüm Programlar > Microsoft SQL Server > Query Analyzer ‘ a giriyoruz.
2 SQL Server’ a bağlanıyoruz ve açılan ekranda ” master ” yerine ” DBmizi ” seçiyoruz.
3 Seçtikten sonra aşağıdaki kodu kopyalayıp , yapıştırıyoruz ve F5 yaparak kodumuzu uyguluyoruz.