| 1.0D版本结婚、多仓库正常可用的SQL修改方法:
--1.--删除 ExtChrData 表后运行创建
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ExtChrData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ExtChrData]
GO
CREATE TABLE [dbo].[ExtChrData] (
[AccountID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Name] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FQBZ] [int] NOT NULL ,
[FQCount] [int] NOT NULL ,
[FQName] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Lores] [int] NOT NULL ,
[LastLores] [int] NULL ,
[Stat] [int] NULL ,
[HYCharStat] [int] NULL
) ON [PRIMARY]
GO
--2.--删除ExtIdData后运行创建ExtIdData表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ExtIdData]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ExtIdData]
GO
CREATE TABLE [dbo].[ExtIdData] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[AccountID] [varchar] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[CountTime] [int] NOT NULL ,
[LastLogin] [datetime] NOT NULL ,
[LastCount] [int] NOT NULL ,
[HaveCents] [int] NOT NULL ,
[HYIDStat] [int] NULL
) ON [PRIMARY]
GO
--3.修改储存过程 MyGSFun_GetExtChrData
--使用霹雳系统的结婚多库储存过程表MyGSFun_GetExtChrData
CREATE proc MyGSFun_GetExtChrData
@AccountID varchar(10),
@Name varchar(10)
as
set nocount on
SELECT ExtChrData.*,WEB_ZS.ZSCS as ZSCount,ExtIdData.HaveCents as Cents
,ExtIdData.HYIdStat as HYIdStat
from ExtChrData
left join WEB_ZS on (ExtChrData.Name=WEB_ZS.NAME)
left join ExtIdData on (ExtIdData.AccountID=@AccountID)
where ExtChrData.AccountID=@accountid and ExtChrData.Name=@name
set nocount off
--4.--修改储存过程 MyGSFun_SetExtChrData
CREATE proc MyGSFun_SetExtChrData
@AccountID varchar(10),
@Name varchar(10),
@FQBZ int,
@FQCount int,
@FQName varchar(10),
@Lores int,
@RTN varchar(100) OUTPUT
as
set nocount on
declare @ec int
,@ll int
,@jf int
,@jfbl int
set @jfbl=2 --设置积分兑换比例 1/X
set @ec=0
begin tran
select @ll=LastLores from extchrdata where accountid=@accountid and name=@name
--转换为积分
if @ll is null
set @jf=cast(@Lores/@jfbl as int)
else
set @jf=cast((@Lores-@ll)/@jfbl as int)
update ExtIdData set HaveCents=HaveCents+@jf where AccountID=@AccountID
--set @ec = @ec | @@error --不管积分更新是否成功都应更新角色数据
if @ll is not null begin
update extchrdata set FQName=@FQName,FQBZ=@FQBZ,FQCount=@FQCount
,Lores=@lores,LastLores=LastLores+@jf*@jfbl
where accountid=@accountid and name=@name
set @ec = @ec | @@error
end else begin
insert into extchrdata (AccountID,Name,FQBZ,FQCount,FQName,Lores,LastLores)
values (@AccountID,@Name,@FQBZ,@FQCount,@FQName,@Lores,@jf*@jfbl)
set @ec = @ec | @@error
end
if @ec = 0 begin
commit tran
set @RTN='OK'
end else begin
rollback tran
set @RTN='Update ExtChrData Error !'
end
set nocount off
GO
--5..--修改储存过程 MyGSFun_SetExtChrData1
create proc MyGSFun_SetExtChrData1
@AccountID varchar(10),
@Name varchar(10),
@FQBZ int,
@FQCount int,
@FQName varchar(10),
@Lores int
as
set nocount on
declare @ec int
,@ll int
,@jf int
,@jfbl int
,@RTN varchar(100)
set @jfbl=2 --设置积分兑换比例 1/X
set @ec=0
begin tran
select @ll=LastLores from extchrdata where accountid=@accountid and name=@name
--转换为积分
if @ll is null
set @jf=cast(@Lores/@jfbl as int)
else
set @jf=cast((@Lores-@ll)/@jfbl as int)
update ExtIdData set HaveCents=HaveCents+@jf where AccountID=@AccountID
--set @ec = @ec | @@error --不管积分更新是否成功都应更新角色数据
if @ll is not null begin
update extchrdata set FQName=@FQName,FQBZ=@FQBZ,FQCount=@FQCount
,Lores=@lores,LastLores=LastLores+@jf*@jfbl
where accountid=@accountid and name=@name
set @ec = @ec | @@error
end else begin
insert into extchrdata (AccountID,Name,FQBZ,FQCount,FQName,Lores,LastLores)
values (@AccountID,@Name,@FQBZ,@FQCount,@FQName,@Lores,@jf*@jfbl)
set @ec = @ec | @@error
end
if @ec = 0 begin
commit tran
set @RTN='OK'
end else begin
rollback tran
set @RTN='Update ExtChrData Error !'
end
select @RTN RTN
set nocount off
GO
|
