[Help] MsSql 2005 Express Procedure Help

[zenocide]

Hi...

I have a sql 2005 database called KN_online..

I have a stored procedure called Account_Login

USE [KN_online]
GO
/****** Object:  StoredProcedure [dbo].[ACCOUNT_LOGIN]    Script Date: 07/16/2008 01:01:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[ACCOUNT_LOGIN]
@AccountID    varchar(21),
@Password    varchar(13),
@nRet        smallint    OUTPUT

AS

DECLARE @Nation tinyint, @CharNum smallint
SET @Nation = 0
SET @CharNum = 0
DECLARE @pwd varchar(13)
SET @pwd = null
SELECT @pwd = strPasswd FROM [dbo].[TB_USER] WHERE strAccountID = @AccountID  and  idays>0
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

exec rundupecheck @accountid
exec rundupecheckinn @accountid

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 = 1
    RETURN
END

and I have a table called Premium_Service

USE [KN_online]
GO
/****** Object:  Table [dbo].[PREMIUM_SERVICE]    Script Date: 07/16/2008 01:02:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PREMIUM_SERVICE](
    [strAccountID] [varchar](27) NOT NULL,
    [strType] [smallint] NOT NULL,
    [nDays] [smallint] NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

In the table premium service, I have a problem when a user get to 0 days. This will stop a application from working correctly when they try to login.

Ive been trying to add a check into the account_login procedure to check the Premium_service table against the users login id, and if they have 0 days when they next try to login the login procedure would then delete that user from the premium_service table.

However everything I do doesnt work..

If anyone could help me on this subject I will be most happy..

Thanks in advance for any guidence.

[zenocide]

I am not too well off on sql Grammering/Launguage, but I have a general idea of what the check would consist of..

Basically it would need to be something along these lines:

begin
check table PREMIUM_SERVICE where StrAccountID = @AccountID and nDays='0' 

if nDays='0'

delete from table PREMIUM_SERVICE where StrAccountID = @AccountID
end

Hope this can better allow you to understand what I need.

Thanks in advance..

Zeno

[zenocide]

Ive also tried this:

Begin
select * 
from 
    PREMIUM_SERVICE 
where 
    StrAccountID= @Accountid and nDays='0'
if nDays='0'
Delete From PREMIUM_SERVICE where StrAccountID= @Accountid
End

No success.

[zenocide]

I came up with this however I seem to be having problems getting sql to compare the 2 results..

select @Accountid from PREMIUM_SERVICE where strAccountID = @Accountid 
select nDays from PREMIUM_SERVICE where nDays = '0' 
Delete From PREMIUM_SERVICE where StrAccountID = @Accountid

Executing this script deletes the user no matter how many days they have left.

[WhoAmI10202]

I assume you are working on a game server.
To completly disable PREMIUM_SERVICE's action, add this:

UPDATE PREMIUM_SERVICE SET nDays='365';

This should make all of the accounts have 365 days left, without having to delete the user out of the database.

Another solution is declaring a variable called DaysLeft as integer, then:

SELECT @DaysLeft=nDays FROM PREMIUM_SERVICE WHERE strAccountID=@AccountID;
if @DaysLeft = 0 /* Although, you may want to set it to 1 to avoid program crashes at all costs */
BEGIN
DELETE FROM PREMIUM_SERVICE WHERE strAccountID=@AccountID;
END

The second solution should be what you wanted.