[Paylaşım] Quest Durumu ve Skill Point Durumu Öğrenme & Düzenleme [twostars]

CREATE PROCEDURE [dbo].[VIEW_QUESTS]

(

@strUserId varchar(21)

)

AS

DECLARE @binQuest binary(400), @sQuestCount smallint, @tmpQuestId smallint, @tmpQuestStatus tinyint, @tmpStrStatus varchar(9), @i int

BEGIN

SET NOCOUNT ON



SELECT @binQuest = CAST(strQuest as varbinary(400)), @sQuestCount = sQuestCount FROM USERDATA WHERE strUserId = @strUserId



CREATE TABLE #user_quests

(

pos int,

sQuestID int,

bQuestStatus tinyint,

strStatus varchar(9)

)



SET @i = 1



WHILE (@i < (@sQuestCount * 3))

BEGIN

SET @tmpQuestId = SUBSTRING(@binQuest, @i, 2) + SUBSTRING(@binQuest, @i, 1)

SET @tmpQuestStatus = SUBSTRING(@binQuest, @i + 2, 1)

SET @tmpStrStatus = 'Unknown'



IF (@tmpQuestStatus = 0)

BEGIN

SET @tmpStrStatus = 'None'

END

ELSE IF (@tmpQuestStatus = 1)

BEGIN

SET @tmpStrStatus = 'Started'

END

ELSE IF (@tmpQuestStatus = 2)

BEGIN

SET @tmpStrStatus = 'Completed'

END



IF (@tmpQuestId != 0)

BEGIN

INSERT INTO #user_quests VALUES(@i, @tmpQuestId, @tmpQuestStatus, @tmpStrStatus)

END

SET @i = @i + 3

END



SET NOCOUNT OFF

SELECT * FROM #user_quests



SET NOCOUNT ON

DROP TABLE #user_quests

END

Genişletmek için tıkla ...

EXEC VIEW_QUESTS 'Schiller'

Genişletmek için tıkla ...

REATE PROCEDURE [dbo].[EDIT_QUEST]

(

@strUserId varchar(21),

@sQuestId smallint,

@bQuestStatus tinyint

)

AS

DECLARE @binQuest binary(400), @sQuestCount smallint, @tmpQuestId smallint, @tmpQuestStatus tinyint, @strQuest varchar(400), @i int, @freePos int, @bFound tinyint

BEGIN

SELECT @binQuest = CAST(strQuest as varbinary(400)), @sQuestCount = sQuestCount FROM USERDATA WHERE strUserId = @strUserId



SET @freePos = null

SET @bFound = 0

SET @i = 1



IF (@binQuest IS NULL)

SET @binQuest = CONVERT(varbinary(400), 0)



WHILE (@i < 400 AND @bFound = 0)

BEGIN

SET @tmpQuestId = SUBSTRING(@binQuest, @i, 2) + SUBSTRING(@binQuest, @i, 1)

SET @tmpQuestStatus = SUBSTRING(@binQuest, @i + 2, 1)



IF (@tmpQuestId = @sQuestId)

BEGIN

SET @bFound = 1

IF (@bQuestStatus IS NULL)

BEGIN

SET @sQuestCount = @sQuestCount - 1

SET @sQuestId = 0

SET @bQuestStatus = 0

END



SET @strQuest = SUBSTRING(@binQuest, 1, @i - 1)

+ CAST(SUBSTRING(CONVERT(varbinary(2), @sQuestId), 2, 1) AS varbinary(1))

+ CAST(SUBSTRING(CONVERT(varbinary(2), @sQuestId), 1, 1) AS varbinary(1))

+ CAST(@bQuestStatus as varbinary(1))

+ SUBSTRING(@binQuest, @i + 3, 400 - (@i + 3))

END

ELSE IF (@tmpQuestId = 0 AND @freePos IS NULL)

BEGIN

SET @freePos = @i

END



SET @i = @i + 3

END



IF (@bFound = 0 AND @freePos IS NOT NULL)

BEGIN

SET @sQuestCount = @sQuestCount + 1

SET @strQuest = SUBSTRING(@binQuest, 1, @freePos - 1)

+ CAST(SUBSTRING(CONVERT(varbinary(2), @sQuestId), 2, 1) AS varbinary(1))

+ CAST(SUBSTRING(CONVERT(varbinary(2), @sQuestId), 1, 1) AS varbinary(1))

+ CAST(@bQuestStatus as varbinary(1))

+ SUBSTRING(@binQuest, @freePos + 3, 400 - (@freePos + 3))

END





IF (@bFound = 0 AND @freePos IS NULL)

BEGIN

PRINT 'Could not insert quest ' + CONVERT(varchar, @sQuestID) + ' with status ' + CONVERT(varchar, @bQuestStatus) + ' as there''s no room left.'

END

ELSE

BEGIN

UPDATE USERDATA SET strQuest = @strQuest, sQuestCount = @sQuestCount WHERE strUserId = @strUserId

END

END

Genişletmek için tıkla ...

XEC EDIT_QUEST 'Schiller', 50, 1 => Kamyon Nickli Oyuncunun 50 Numaralı Questi Bitirdiğini Kaydeder.

EXEC EDIT_QUEST 'Schiller', 50, null => Kamyon Nickli Oyuncunun 50 Numaralı Quest'ini Siler.

EXEC EDIT_QUEST 'Schiller', 30, 2 => Kamyon Nicki Oyuncunun 30 Numaralı Quest'i Bitirdiğini Kaydeder.

Genişletmek için tıkla ...

CREATE PROCEDURE [dbo].[VIEW_SKILLS]

(

@strUserId varchar(21)

)

AS

DECLARE @binSkill binary(10), @Free tinyint, @Cat1 tinyint, @Cat2 tinyint, @Cat3 tinyint, @Masters tinyint

BEGIN

SELECT @binSkill = CAST(strSkill as varbinary(10)) FROM USERDATA WHERE strUserId = @strUserId



SET @Free = SUBSTRING(@binSkill, 1, 1)

SET @Cat1 = SUBSTRING(@binSkill, 6, 1)

SET @Cat2 = SUBSTRING(@binSkill, 7, 1)

SET @Cat3 = SUBSTRING(@binSkill, 8, 1)

SET @Masters = SUBSTRING(@binSkill, 9, 1)



SELECT @Free as Free, @Cat1 as Cat1, @Cat2 as Cat2, @Cat3 as Cat3, @Masters as Masters

END

Genişletmek için tıkla ...

EXEC VIEW_SKILLS 'Schiller'

Genişletmek için tıkla ...

CREATE PROCEDURE [dbo].[EDIT_SKILLS]

(

@strUserId varchar(21),

@newFree tinyint,

@newCat1 tinyint,

@newCat2 tinyint,

@newCat3 tinyint,

@newMasters tinyint

)

AS

DECLARE @binSkill binary(10), @Free tinyint, @Cat1 tinyint, @Cat2 tinyint, @Cat3 tinyint, @Masters tinyint, @strSkill varchar(10)

BEGIN

SELECT @binSkill = CAST(strSkill as varbinary(10)) FROM USERDATA WHERE strUserId = @strUserId



SET @Free = SUBSTRING(@binSkill, 1, 1)

SET @Cat1 = SUBSTRING(@binSkill, 6, 1)

SET @Cat2 = SUBSTRING(@binSkill, 7, 1)

SET @Cat3 = SUBSTRING(@binSkill, 8, 1)

SET @Masters = SUBSTRING(@binSkill, 9, 1)



IF (@newFree IS NOT NULL)

SET @Free = @newFree



IF (@newCat1 IS NOT NULL)

SET @Cat1 = @newCat1



IF (@newCat2 IS NOT NULL)

SET @Cat2 = @newCat2



IF (@newCat3 IS NOT NULL)

SET @Cat3 = @newCat3



IF (@newMasters IS NOT NULL)

SET @Masters = @newMasters



SET @strSkill = CONVERT(VARBINARY(1), @Free) /* free points */

+ CONVERT(VARBINARY(4), 0) /* leadership, politics, etc */

+ CONVERT(VARBINARY(1), @Cat1) /* cat 1 */

+ CONVERT(VARBINARY(1), @Cat2) /* cat 2 */

+ CONVERT(VARBINARY(1), @Cat3) /* cat 3 */

+ CONVERT(VARBINARY(1), @Masters) /* masters */

+ CONVERT(VARBINARY(1), 0) /* padding */



UPDATE USERDATA SET strSkill = @strSkill WHERE strUserId = @strUserId

END

Genişletmek için tıkla ...