ctgwglzc 发表于 2008-11-5 09:08:38

新的LOGIN存储，谁能看出有无错误

CREATE PROCEDURE ACCOUNT_LOGIN
@AccountID    varchar(21),
@Password    varchar(13),
@nRet      smallint    OUTPUT

AS
declare @banint , @ban1 int, @ban2 int
select @nRet = count(straccountid) from currentuser
select @ban = authority from userdata where struserid = (select strcharid1 from account_char where straccountid = @AccountID and strcharid1 is not null )
select @ban1 = authority from userdata where struserid = (select strcharid2 from account_char where straccountid = @AccountID and strcharid1 is not null )
select @ban2 = authority from userdata where struserid = (select strcharid3 from account_char where straccountid = @AccountID and strcharid1 is not null )

if @ban = 255 or @ban1 = 255 or @ban2 = 255
begin
Set @nRet = 4
        RETURN
end
declare @gankss varchar(21)
select @nRet = count(straccountid) from currentuser
select @gankss = count(strAccountID) from premium_service where strAccountID = @AccountID
if @gankss =0 and @nRet>100
begin
Set @nRet = 0
end

DELETE FROM CURRENTUSER WHERE strAccountID = @AccountID

exec rundupecheckinn @AccountID

DECLARE @Nation tinyint, @CharNum smallint
SET @Nation = 0
SET @CharNum = 0

DECLARE @pwd varchar(13)

SET @pwd = null

SELECT @pwd = strPasswd FROM . WHERE strAccountID = @AccountID and idays=6
IF @pwd IS null
BEGIN
    SET @nRet = 0
             --SET @nRet = 4
    RETURN
END

ELSE IF @pwd <> @Password
BEGIN
    SET @nRet = 0
             --SET @nRet = 3
    RETURN
END

DECLARE @gokhantasci varchar(21)
select @gokhantasci = count(straccountid)FROM premium_service WHERE strAccountID = @AccountID

if @gokhantasci = 0
begin
insert into PREMIUM_SERVICE (strAccountID, strType, nDays) VALUES (@AccountID, 1, 3)
end

SELECT @Nation = bNation, @CharNum = bCharNum FROM ACCOUNT_CHAR WHERE strAccountID = @AccountID
IF @@ROWCOUNT = 0
BEGIN
    SET @nRet = 1
    RETURN
END
IF @CharNum = 0
BEGIN
    SET @nRet = 1
    RETURN
END
ELSE
BEGIN
    SET @nRet = @Nation+1
             --SET @nRet = 1
    RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

binwang 发表于 2008-11-5 09:59:47

逻辑还是语法错误?

feike 发表于 2008-11-5 11:22:11

跟帖做个简要解释，有问号的地方具有不确定性。有不对的请指正，我没弄明白也请高手们指教。

CREATE PROCEDURE ACCOUNT_LOGIN
@AccountID varchar(21), ;接收账户信息
@Password varchar(13),；接收密码信息
@nRet smallint OUTPUT   ；设置为输出

AS
declare @ban int , @ban1 int, @ban2 int；定义ban ban1 ban2
select @nRet = count(straccountid) from currentuser;在currentuser表内查询有否输入的账户名
select @ban = authority from userdata where struserid = (select strcharid1 from account_char where straccountid = @AccountID and strcharid1 is not null )；在userdata表内查询账户的ban信息
select @ban1 = authority from userdata where struserid = (select strcharid2 from account_char where straccountid = @AccountID and strcharid1 is not null )；在userdata表内查询账户的ban1信息
select @ban2 = authority from userdata where struserid = (select strcharid3 from account_char where straccountid = @AccountID and strcharid1 is not null )；在userdata表内查询账户的ban2信息

if @ban = 255 or @ban1 = 255 or @ban2 = 255;如果ban是被停权账户则设置nRet=4(不给登陆或显示账户停权信息)
begin
Set @nRet = 4
RETURN
end

declare @gankss varchar(21) ；定义变量gankss
select @nRet = count(straccountid) from currentuser；在currentuser表内查询账户信息
select @gankss = count(strAccountID) from premium_service where strAccountID = @AccountID；在premium_service表内查询账户信息
if @gankss = 0 and @nRet>100；如果表premium_service无此账户信息以及表currentuser内的账户信息>100则允许进入？
begin
Set @nRet = 0
end

DELETE FROM CURRENTUSER WHERE strAccountID = @AccountID；删除currentuser表内的账户信息

exec rundupecheckinn @AccountID；执行？？

DECLARE @Nation tinyint, @CharNum smallint；定义变量
SET @Nation = 0 ；设置为0
SET @CharNum = 0；设置为0

DECLARE @pwd varchar(13)；定义密码字段长度为13

SET @pwd = null;设定密码默认值为空

SELECT @pwd = strPasswd FROM . WHERE strAccountID = @AccountID and idays=6；查询比较密码
IF @pwd IS null；如果查询无结果，返回密码错误信息
BEGIN
SET @nRet = 0
--SET @nRet = 4
RETURN
END

ELSE IF @pwd <> @Password；如果密码不相符，返回密码错误信息
BEGIN
SET @nRet = 0
--SET @nRet = 3
RETURN
END

DECLARE @gokhantasci varchar(21)；定义
select @gokhantasci = count(straccountid) FROM premium_service WHERE strAccountID = @AccountID；在premium_service表内查询账户信息

if @gokhantasci = 0；如果表内无此账户信息则插入该账户信息
begin
insert into PREMIUM_SERVICE (strAccountID, strType, nDays) VALUES (@AccountID, 1, 3)
end

SELECT @Nation = bNation, @CharNum = bCharNum FROM ACCOUNT_CHAR WHERE strAccountID = @AccountID；在ACCOUNT_CHAR表内查询国家类别

；下面这些没弄明白，但我知道是与国家（种族）的选择有关
IF @@ROWCOUNT = 0；
BEGIN
SET @nRet = 1
RETURN
END
IF @CharNum = 0；
BEGIN
SET @nRet = 1
RETURN
END
ELSE
BEGIN
SET @nRet = @Nation+1
--SET @nRet = 1
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF ；关闭？？
GO
SET ANSI_NULLS ON ；设置？？
GO

yl941 发表于 2008-11-5 11:43:54

本身没错误，这里需要强制排序就可以了！
declare @ban int , @ban1 int, @ban2 int
select @nRet = count(straccountid) from currentuser
select @ban = authority from userdata where struserid = (select strcharid1 from account_char where straccountid = @AccountID and strcharid1 is not null )
select @ban1 = authority from userdata where struserid = (select strcharid2 from account_char where straccountid = @AccountID and strcharid1 is not null )
select @ban2 = authority from userdata where struserid = (select strcharid3 from account_char where straccountid = @AccountID and strcharid1 is not null )

yl941 发表于 2008-11-5 11:50:15

改成如下即可正常使用
declare @ban int , @ban1 int, @ban2 int
select @nRet = count(straccountid) from currentuser
select @ban = authority from userdata where struserid collate database_default= (select strcharid1 from account_char where straccountid collate database_default= @AccountID and strcharid1 is not null )
select @ban1 = authority from userdata where struserid collate database_default= (select strcharid2 from account_char where straccountid collate database_default= @AccountID and strcharid1 is not null )
select @ban2 = authority from userdata where struserid collate database_default= (select strcharid3 from account_char where straccountid collate database_default= @AccountID and strcharid1 is not null )

rainbody 发表于 2008-11-5 16:21:27

学习的帖子要顶

ymhuang 发表于 2008-11-5 18:05:35

要这么多ban来干什么呢？tb_user里面不是有一列是authority吗？
页: [1]
查看完整版本: 新的LOGIN存储，谁能看出有无错误