use code in mssql
Quote:
/****** Object: Stored Procedure dbo.spBuyBountyItem Script Date: 5/10/2007 3:41:58 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spBuyBountyItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spBuyBountyItem]
GO
Quote:
/****** Object: Stored Procedure dbo.spBuyBountyItem Script Date: 5/10/2007 3:42:11 PM ******/
CREATE PROC [spBuyBountyItem]
@CID INT,
@ItemID INT,
@Price INT
AS
SET NOCOUNT ON
BEGIN
DECLARE @OrderCIID int
DECLARE @Bounty INT
BEGIN TRAN
-- 잔액검사
SELECT @Bounty=BP FROM Character(NOLOCK) WHERE CID=@CID
IF @Bounty IS NULL OR @Bounty < @Price
BEGIN
ROLLBACK TRAN
RETURN 0
END
-- Bounty 감소
UPDATE Character SET BP=BP-@Price WHERE CID=@CID
IF 0 = @@ROWCOUNT
BEGIN
ROLLBACK TRAN
RETURN (-1)
END
-- Item 추가
INSERT INTO CharacterItem (CID, ItemID, RegDate) Values (@CID, @ItemID, GETDATE())
IF 0 <> @@ERROR
BEGIN
ROLLBACK TRAN
RETURN (-1)
END
SELECT @OrderCIID = @@IDENTITY
-- Item 구매로그 추가
INSERT INTO ItemPurchaseLogByBounty (ItemID, CID, Date, Bounty, CharBounty, Type)
VALUES (@ItemID, @CID, GETDATE(), @Price, @Bounty, '구입')
IF 0 <> @@ERROR BEGIN
ROLLBACK TRAN
RETURN (-1)
END
SELECT @OrderCIID as ORDERCIID
COMMIT TRAN
RETURN 1
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Quote:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Item_PurchaseItemByBountyHistory_FK20050314]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ItemPurchaseLogByBounty] DROP CONSTRAINT Item_PurchaseItemByBountyHistory_FK20050314
GO
Quote:
/****** Object: Stored Procedure dbo.spBuyBountyItem Script Date: 5/10/2007 3:41:58 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spBuyBountyItem]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spBuyBountyItem]
GO
Quote:
/****** Object: Stored Procedure dbo.spBuyBountyItem Script Date: 5/10/2007 3:42:11 PM ******/
CREATE PROC [spBuyBountyItem]
@CID INT,
@ItemID INT,
@Price INT
AS
SET NOCOUNT ON
BEGIN
DECLARE @OrderCIID int
DECLARE @Bounty INT
BEGIN TRAN
-- 잔액검사
SELECT @Bounty=BP FROM Character(NOLOCK) WHERE CID=@CID
IF @Bounty IS NULL OR @Bounty < @Price
BEGIN
ROLLBACK TRAN
RETURN 0
END
-- Bounty 감소
UPDATE Character SET BP=BP-@Price WHERE CID=@CID
IF 0 = @@ROWCOUNT
BEGIN
ROLLBACK TRAN
RETURN (-1)
END
-- Item 추가
INSERT INTO CharacterItem (CID, ItemID, RegDate) Values (@CID, @ItemID, GETDATE())
IF 0 <> @@ERROR
BEGIN
ROLLBACK TRAN
RETURN (-1)
END
SELECT @OrderCIID = @@IDENTITY
-- Item 구매로그 추가
INSERT INTO ItemPurchaseLogByBounty (ItemID, CID, Date, Bounty, CharBounty, Type)
VALUES (@ItemID, @CID, GETDATE(), @Price, @Bounty, '구입')
IF 0 <> @@ERROR BEGIN
ROLLBACK TRAN
RETURN (-1)
END
SELECT @OrderCIID as ORDERCIID
COMMIT TRAN
RETURN 1
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
credits by me
