美丽世界online私服数据库存储过程（新人送点,送钱的存储过程）

GO

USE NAGEBOARD

GO

IF EXISTS(SELECT name from sysobjects WHERE name='crttable_trade' AND type='p')

DROP PROCEDURE crttable_trade

GO

USE NAGEBOARD

GO

CREATE proc crttable_trade

as

declare @sql1 varchar(200) 

declare @sql2 varchar(400) 

declare @sql3 varchar(200) 

declare @sql4 varchar(800) 

declare @nalja varchar(120) 

set @nalja= convert(varchar(30), getdate(),12)

set @sql1='CREATE TABLE itemtrade'+@nalja

set @sql2='([ii] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY, [logTime] [datetime] NOT NULL default (getdate()), [logCode] [int] NOT NULL, [nameChar] char(16) NOT NULL default (0), [propCharID] [int] NOT NULL, [recvCharID] [int] NOT NULL, [itemid] [int] NOT NULL, [prodid] [int] NOT NULL, [nMoney][int] NOT NULL default(0), [map] [int] NOT NULL )'

set @sql3=' ON [PRIMARY]'

set @sql4=@sql1+@sql2+@sql3

exec (@sql4)

declare @ix_code varchar(40) 

declare @ix_char varchar(40) 

declare @ix_sql varchar(200) 

set @ix_code = 'ix_code_1_'+@nalja

set @ix_char = 'ix_char_1_'+@nalja

set @ix_sql = 'create index '+@ix_code+ ' on '+ 'itemtrade'+@nalja+' (logCode)'

exec( @ix_sql)

set @ix_sql = 'create index '+@ix_char+ ' on '+ 'itemtrade'+@nalja+' (propCharID)'

exec( @ix_sql)

GO

USE NAGEBOARD

GO

IF EXISTS(SELECT name from sysobjects WHERE name='deltable_item1' AND type='p')

DROP PROCEDURE deltable_item1

GO

USE NAGEBOARD

GO

CREATE proc deltable_item1

as 

declare @nalja varchar(12) 

declare @sql varchar(120) 

set @nalja= convert(varchar(30), getdate()-1,12)

set @sql='drop table itemtrade'+@nalja 

exec (@sql)

GO

USE NAGEMEMBER

GO

IF EXISTS(SELECT name from sysobjects WHERE name='CreateTable_LoginLog' AND type='p')

DROP PROCEDURE CreateTable_LoginLog

GO

USE NAGEMEMBER

GO

CREATE proc CreateTable_LoginLog

as

declare @day3 datetime

declare @nalja3 varchar(10)

declare @sql1 varchar(255)

declare @sql2 varchar(255)

set @day3 = (select dateadd(day,0,getdate()))

set @nalja3=(select convert(varchar(4), datepart(yyyy, @day3))+convert(varchar(4), datepart(mm, @day3))+convert(varchar(4), datepart(dd, @day3)))

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Loginlog_'+@nalja3+']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

  begin

    return

  end

else

  begin

    set @sql1=' CREATE TABLE Loginlog_'+@nalja3+' ( login_id varchar(20) Not Null, type int null, status int, time datetime, '

    set @sql2=' ip varchar(20), propid int not null, kind tinyint ) '         

    exec (@sql1 + @sql2)

  end

GO

USE NAGEMEMBER

GO

IF EXISTS(SELECT name from sysobjects WHERE name='Delete_LoginLog' AND type='p')

DROP PROCEDURE Delete_LoginLog

GO

USE NAGEMEMBER

GO

CREATE proc Delete_LoginLog

as

declare @nalja varchar(10)

declare @sql1 varchar(255)

set @nalja=(select convert(varchar(4), datepart(yyyy, dateadd(dd,-1,getdate())))+convert(varchar(4), datepart(mm, dateadd(dd,-1,getdate())))+convert(varchar(4), datepart(dd, dateadd(dd,-1,getdate()))))

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Loginlog_'+@nalja+']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

begin

  set @sql1=' drop table Loginlog_'+@nalja+' '

  exec (@sql1)

end

GO

USE NAGEMEMBER

GO

IF EXISTS(SELECT name from sysobjects WHERE name='login_loginsert' AND type='TR')

DROP PROCEDURE login_loginsert

GO

USE NAGEMEMBER

GO

CREATE trigger login_loginsert

on login_log

for insert 

as 

exec CreateTable_LoginLog

exec Delete_LoginLog

exec nageboard.dbo.crttable 

GO

USE NAGE

GO

IF EXISTS(SELECT name from sysobjects WHERE name='SetChrTime' AND type='p')

DROP PROCEDURE SetChrTime

GO

USE NAGE

GO

CREATE PROCEDURE SetChrTime

@id int,

@state tinyint

AS

declare @logintime datetime

declare @outtime datetime

declare @charname varchar(16)

declare @tmp int

declare @loginid varchar(16)

declare @int int

IF( @state = 1)

BEGIN

UPDATE CharInfo SET glogin = GetDate() WHERE charPropID = @id

END

ELSE

BEGIN

    select @logintime=glogin from charinfo where charPropID = @id

    set @outtime=getdate()+1

    select @charname=charname,@loginid=id_loginid from nagemember.dbo.charname_1 where charPropID = @id

   set @tmp=datediff(hh,@logintime,@outtime)

   set @int=1

   while @int<=@tmp

   begin

       update charinfo set money=money+50000000 where charPropID = @id and money<1940000000

       if(@int%2 =0)

       begin

           insert into nagemember.dbo.item_to_game(recv_charid,recv_name,recv_serv,recv_loginid,itemid) values(@id,@charname,11,@loginid,45516)

       end

       if (@int%3=0)

       begin

           insert into nagemember.dbo.item_to_game(recv_charid,recv_name,recv_serv,recv_loginid,itemid) values(@id,@charname,11,@loginid,43511)

       end

       if (@int%4=0)

       begin

           insert into nagemember.dbo.item_to_game(recv_charid,recv_name,recv_serv,recv_loginid,itemid) values(@id,@charname,11,@loginid,43528)

       end

       if (@int%5=0)

       begin

           insert into nagemember.dbo.item_to_game(recv_charid,recv_name,recv_serv,recv_loginid,itemid) values(@id,@charname,11,@loginid,43513)

       end

       if (@int%6=0)

       begin

           insert into nagemember.dbo.item_to_game(recv_charid,recv_name,recv_serv,recv_loginid,itemid) values(@id,@charname,11,@loginid,43520)

       end

       if (@int%7=0)

       begin

           insert into nagemember.dbo.item_to_game(recv_charid,recv_name,recv_serv,recv_loginid,itemid) values(@id,@charname,11,@loginid,43511)

       end

       set @int=@int+1

   end

    UPDATE CharInfo SET glogout = GetDate() WHERE charPropID = @id

END

GO

USE NAGE

GO

IF EXISTS(SELECT name from sysobjects WHERE name='SetChrValue' AND type='p')

DROP PROCEDURE SetChrValue

GO

USE NAGE

GO

CREATE PROCEDURE SetChrValue 

@id     int,

@lv     smallint,

@exp     bigint,

@eclv     tinyint,

@ecexp   int,

@hp     smallint,

@sp     smallint,

@stm     smallint,

@str     smallint,

@dex     smallint,

@esp     smallint,

@spt     smallint,

@int     smallint,

@mn     int,

@bmn     int,

@lic     tinyint,

@wth     tinyint,

@spend   tinyint,

@snext     int,

@flv     tinyint,

@fexp     int,

@tradestr   int,

@uid     smallint,

@omap   tinyint,

@otype   tinyint,

@restr     tinyint,

@radd     tinyint,

@rtime     int,

@rdec     int,

@cr     tinyint,

@crp     smallint,

@crd     int,

@pk     tinyint,

@cc     tinyint,

@cm     tinyint,

@wx     smallint,

@wz     smallint,

@bd     smallint,

@lay     tinyint,

@lvp     smallint,

@skp     smallint,

@natt     tinyint,

@ub     tinyint,

@hero     tinyint,

@chat     tinyint,

@exch     tinyint,

@hide     tinyint,

@pri     tinyint,

@fri     tinyint,

@mgage   tinyint,

@mprob   tinyint,

@mpnt     smallint

AS

if (@lv=1 and @lvp=0 and @mn=0 and @str=23 and @dex=22)

begin

  set @lvp=300

  set @mn=50000000

end

else if (@lv=1 and @lvp=0 and @mn=0 and @esp=23 and @spt=22)

begin

  set @lvp=300

  set @mn=50000000

end

UPDATE 

CharInfo

SET

Lv     = @lv,

Exp     = @exp,

eclv     = @eclv,

ecExp     = @ecExp,

HP     = @hp,

SP     = @sp,

STM     = @stm,

Str     = @str,

Dex     = @dex,

Esp     = @esp,

Spt     = @spt,

Int     = @int,

money     = @mn,

bankmoney   = @bmn,

lic     = @lic,

wth     = @wth,

spend     = @spend,

snext     = @snext,

flv     = @flv,

fexp     = @fexp,

tradestr     = @tradestr,

uid     =@uid,

omap     = @omap,

otype     = @otype,

restr     = @restr,

restr_add   = @radd,

restr_time   = @rtime,

restr_dec   = @rdec,

crime     = @cr,

cr_pnt     = @crp,

cr_dec     = @crd,

pk     = @pk,

cCity     = @cc,

cMap     = @cm,

x     = @wx,

z     = @wz,

struct     = @bd,

layer     = @lay,

lvpoint       = @lvp,

skpoint     = @skp,

natt     = @natt,

ubattle     = @ub,

hero     = @hero,

chat_flag   = @chat,

exch_flag   = @exch,

hide_flag   = @hide,

bPrivate   = @pri,

bFriend     = @fri,

mission_gage   = @mgage,

mission_prob   = @mprob,

mission_pnt   = @mpnt

WHERE

charPropID   = @id

GO