x-MU.net форум

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[WZ_PeriodItemSelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[WZ_PeriodItemSelect]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[T_PeriodItemInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T_PeriodItemInfo]
GO

CREATE TABLE [dbo].[T_PeriodItemInfo] (
[PeriodIndex] [int] IDENTITY (1, 1) NOT NULL ,
[UserGuid] [int] NOT NULL ,
[CharacterName] [char] (10) COLLATE Korean_Wansung_CI_AS NOT NULL ,
[ItemCode] [int] NOT NULL ,
[EffectType1] [tinyint] NOT NULL ,
[EffectType2] [tinyint] NULL ,
[UsedTime] [int] NOT NULL ,
[LeftTime] [int] NOT NULL ,
[BuyDate] [smalldatetime] NOT NULL ,
[ExpireDate] [smalldatetime] NOT NULL ,
[UsedInfo] [tinyint] NOT NULL ,
[OptionType] [tinyint] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[T_PeriodItemInfo] ADD
CONSTRAINT [DF_T_PeriodItemInfo_UsedTime] DEFAULT (0) FOR [UsedTime],
CONSTRAINT [DF_T_PeriodItemInfo_UsedInfo] DEFAULT (0) FOR [UsedInfo],
CONSTRAINT [DF_T_PeriodItemInfo_OptionType] DEFAULT (2) FOR [OptionType]
GO

CREATE UNIQUE INDEX [PK_PeriodItemInfo] ON [dbo].[T_PeriodItemInfo]([PeriodIndex]) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure dbo.WZ_PeriodItemSelect
@UserGuid int,
@CharacterName varchar(10)
as
BEGIN
DECLARE @ErrorCode int
DECLARE @ItemInfoCount int
DECLARE @PeriodIndex int
DECLARE @ExpireDate smalldatetime
DECLARE @UsedInfo tinyint

SET @PeriodIndex = 0
SET @ErrorCode = 0
SET @ItemInfoCount = 0
SET @UsedInfo = 0

Set nocount on

DECLARE CUR CURSOR FOR SELECT [PeriodIndex], [ExpireDate], [UsedInfo] FROM T_PeriodItemInfo WHERE UserGuid = @UserGuid AND CharacterName = @CharacterName AND UsedInfo = 1 FOR UPDATE
OPEN CUR
FETCH NEXT FROM CUR INTO @PeriodIndex, @ExpireDate, @UsedInfo
WHILE( @@fetch_status <> -1 )
BEGIN
IF( @@fetch_status <> -2 )
BEGIN
IF( @ExpireDate < GetDate() )
BEGIN
UPDATE T_PeriodItemInfo SET UsedInfo = 0 WHERE PeriodIndex = @PeriodIndex
END
END
FETCH NEXT FROM CUR INTO @PeriodIndex, @ExpireDate, @UsedInfo
END

-- é...'æ�žè¢� 父丰 è€�磊啊 巢篮 é...'æ�žè¢�é˜' å...�仿矫难霖促.

SELECT *, DATEDIFF( minute, BuyDate, GETDATE() ) AS UsedMinutes, DATEDIFF( minute, GETDATE(), ExpireDate ) AS LeftMinutes FROM T_PeriodItemInfo where UserGuid = @UserGuid AND CharacterName = @CharacterName AND UsedInfo = 1
--SELECT * FROM T_PeriodItemInfo where UserGuid = @UserGuid AND CharacterName = @CharacterName AND UsedInfo = 1
IF( @@Error <> 0 )
BEGIN
SET @ErrorCode = -1
END

CLOSE CUR
DEALLOCATE CUR

Set nocount off
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO