Stored Procedure for SQL 2000

Results 1 to 4 of 4
  1. 11-11-09 #1
    KalEssence
    Apprentice KalEssence is offline
    MemberRank
    Oct 2009 Join Date
    7Posts

    Stored Procedure for SQL 2000

    Hey all!
    I found this procedure witch one is for SQL 2005. I tested it on the SQL 2000, dosen't work. Can somebody tell me what should i fix, cuz I need it on SQL 2000 =(( and the things would be nice for me.
    Oryginal post is here:

    Thx, w8ing for help :*****
    http://www.gblprotect.net/showthread...ored+Procedure

    Code:
    USE [kal_db]
GO
/****** Object: StoredProcedure [dbo].[SetPrefix] Script Date: 08/29/2009 13:00:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SetPrefix]

@prefix int,
@iid int

AS


declare @pid int
declare @index int
declare @bof_index int
declare @bofindex1 int
SET @pid = (SELECT PID FROM Item WHERE [IID] = @iid)
SET @index = (SELECT [Index] FROM [Item] WHERE [IID] = @iid)
SET @bof_index = (SELECT [BofIndex] FROM [BofTable] WHERE [Index] = @index)

IF (@prefix = 113)
begin
--do a special thing, like make bound
UPDATE [Item] SET Info = (Info|128) WHERE IID = @iid
end

ELSE IF (@prefix = 114)
begin
--do a special thing, like make unbound
UPDATE [Item] SET Info = Info - (Info&128) WHERE IID = @iid
end

ELSE IF (@prefix = 115)
begin
--Stone of Demon Blood G3
UPDATE [Item] SET Info = (Info|1310912) WHERE IID = @iid
end

ELSE IF (@prefix = 116)
begin
--Stone of Shadow G1
UPDATE [Item] SET Info = (Info|524672) WHERE IID = @iid
end

ELSE IF (@prefix = 117)
begin
--Stone of Shadow G2
UPDATE [Item] SET Info = (Info|1048960) WHERE IID = @iid
end

ELSE IF (@prefix = 118)
begin
--Stone of Holy G1
UPDATE [Item] SET Info = (Info|524928) WHERE IID = @iid
end

ELSE IF (@prefix = 119)
begin
--Stone of Holy G2
UPDATE [Item] SET Info = (Info|1049216) WHERE IID = @iid
end

ELSE IF (@prefix = 120)
begin
--Stone of Flame G1
UPDATE [Item] SET Info = (Info|525440) WHERE IID = @iid
end

ELSE IF (@prefix = 121)
begin
--Stone of Flame G2
UPDATE [Item] SET Info = (Info|1049728) WHERE IID = @iid
end

ELSE IF (@prefix = 122)
begin
--Stone of Ice G1
UPDATE [Item] SET Info = (Info|526464) WHERE IID = @iid
end

ELSE IF (@prefix = 123)
begin
--Stone of Ice G2
UPDATE [Item] SET Info = (Info|1050752) WHERE IID = @iid
end

ELSE IF (@prefix = 124)
begin
--Stone of Lightning G1
UPDATE [Item] SET Info = (Info|528512) WHERE IID = @iid
end

ELSE IF (@prefix = 125)
begin
--Stone of Lightning G2
UPDATE [Item] SET Info = (Info|1052800) WHERE IID = @iid
end

ELSE IF (@prefix = 126)
begin
--Stone of Poison G1
UPDATE [Item] SET Info = (Info|532608) WHERE IID = @iid
end

ELSE IF (@prefix = 127)
begin
--Stone of Poison G2
UPDATE [Item] SET Info = (Info|1057016) WHERE IID = @iid
end

ELSE IF (@prefix = 128)
begin
--Stone of Para G1
UPDATE [Item] SET Info = (Info|540910) WHERE IID = @iid
end

ELSE IF (@prefix = 129)
begin
--Stone of Para G2
UPDATE [Item] SET Info = (Info|1065088) WHERE IID = @iid
end

ELSE IF (@prefix = 130)
begin
--Stone of Str G1
UPDATE [Item] SET Info = (Info|557184) WHERE IID = @iid
end

ELSE IF (@prefix = 131)
begin
--Stone of Str G2
UPDATE [Item] SET Info = (Info|1081472) WHERE IID = @iid
end

ELSE IF (@prefix = 132)
begin
--Stone of Skill G1
UPDATE [Item] SET Info = (Info|589952) WHERE IID = @iid
end

ELSE IF (@prefix = 133)
begin
--Stone of Skill G2
UPDATE [Item] SET Info = (Info|1114240) WHERE IID = @iid
end

ELSE IF (@prefix = 134)
begin
--Stone of Mistery G3
UPDATE [Item] SET Info = (Info|1179776) WHERE IID = @iid
end

ELSE IF (@prefix = 135)
begin
--[15]EB
UPDATE [Item] SET UpgrLevel = 15 WHERE IID = @iid
end

ELSE IF (@prefix = 136)
begin
--[10]OTP
UPDATE [Item] SET XHit = 10 WHERE IID = @iid
end

ELSE IF (@prefix = 137)
begin
--[15]Attack
UPDATE [Item] SET XAttack = 15 WHERE IID = @iid
end

ELSE IF (@prefix = 138)
begin
--[15]Magic Attack
UPDATE [Item] SET XMagic = 15 WHERE IID = @iid
end

ELSE IF (@prefix = 139)
begin
--[20]EB [20]Attack [20]OTP
UPDATE [Item] SET UpgrLevel = 20, XHit = 20, XAttack = 20 WHERE IID = @iid
end

ELSE IF (@prefix = 140)
begin
--[15]EB [15]Attack [5]OTP [15]Magic
UPDATE [Item] SET UpgrLevel = 15, XHit = 5, XAttack = 15, XMagic = 15, Prefix = 100 WHERE IID = @iid
end

ELSE IF (@prefix = 141)
begin
--Guild Time Reset
UPDATE [GuildMember] SET Date = 0 WHERE PID = @pid
end

ELSE IF (@prefix = 142)
begin
--20/20/20/20 Red Claw
UPDATE [Item] SET UpgrLevel = 20, XHit = 20, XAttack = 20, Prefix = 101 WHERE IID = @iid
end

ELSE IF (@prefix = 143)
begin
--Eb success chance 100% - UpgradeRate -> 100
UPDATE [Item] SET UpgrRate = 100 WHERE IID = @iid
end

ELSE IF (@prefix = 144)
begin
--5 Def / 1 Eva[Armor] -> Players
UPDATE [Item] SET XDefense = 5, XDodge = 1 WHERE IID = @iid
end

ELSE IF (@prefix = 145)
begin
--10 Def / 1Eva[Armor] -> Gm's
UPDATE [Item] SET XDefense = 10, XDodge = 1 WHERE IID = @iid
end

ELSE IF (@prefix = 146)
begin
--10 Def / 1Eva / Red Claw / Db Mix G3[Armor] -> Gm's (kann bei zu viel parts probleme verursachen GLAUBE ich)
UPDATE [Item] SET XDefense = 10, XDodge = 1, Prefix = **** Info = (Info|1310720) WHERE IID = @iid
end

ELSE IF (@prefix = 147)
begin
--20/20/255 Darkchaos - Pamz
UPDATE [Item] SET UpgrLevel = 255, XHit = 20, XAttack = 20, Prefix = 100 WHERE IID = @iid
end

ELSE IF (@prefix = 148)
begin
if(@bof_index > 1)
SET @bofindex1 = @bof_index
else
SET @bofindex1 = @index

UPDATE Item SET [Index] = @bofindex1 WHERE [IID] = @iid
end

ELSE
begin
--Do the normal query
UPDATE Item SET [Prefix] = @prefix WHERE [IID] = @iid
end
    Reply With Quote Reply With Quote
  2. Check out our sponsor
    Stored Procedure for SQL 2000
  3. 11-11-09 #2
    Munto
    Apprentice Munto is offline
    MemberRank
    Nov 2009 Join Date
    GermanyLocation
    23Posts

    Re: Stored Procedure for SQL 2000

    This should work @mssql 2000 :)


    Code:
    CREATE PROCEDURE SetPrefix


@prefix int,
@iid int

AS


declare @pid int
declare @index int
declare @bof_index int
declare @bofindex1 int
SET @pid = (SELECT PID FROM Item WHERE [IID] = @iid)
SET @index = (SELECT [Index] FROM [Item] WHERE [IID] = @iid)
SET @bof_index = (SELECT [BofIndex] FROM [BofTable] WHERE [Index] = @index)

IF (@prefix = 113)
begin
--do a special thing, like make bound
UPDATE [Item] SET Info = (Info|128) WHERE IID = @iid
end

ELSE IF (@prefix = 114)
begin
--do a special thing, like make unbound
UPDATE [Item] SET Info = Info - (Info&128) WHERE IID = @iid
end

ELSE IF (@prefix = 115)
begin
--Stone of Demon Blood G3
UPDATE [Item] SET Info = (Info|1310912) WHERE IID = @iid
end

ELSE IF (@prefix = 116)
begin
--Stone of Shadow G1
UPDATE [Item] SET Info = (Info|524672) WHERE IID = @iid
end

ELSE IF (@prefix = 117)
begin
--Stone of Shadow G2
UPDATE [Item] SET Info = (Info|1048960) WHERE IID = @iid
end

ELSE IF (@prefix = 118)
begin
--Stone of Holy G1
UPDATE [Item] SET Info = (Info|524928) WHERE IID = @iid
end

ELSE IF (@prefix = 119)
begin
--Stone of Holy G2
UPDATE [Item] SET Info = (Info|1049216) WHERE IID = @iid
end

ELSE IF (@prefix = 120)
begin
--Stone of Flame G1
UPDATE [Item] SET Info = (Info|525440) WHERE IID = @iid
end

ELSE IF (@prefix = 121)
begin
--Stone of Flame G2
UPDATE [Item] SET Info = (Info|1049728) WHERE IID = @iid
end

ELSE IF (@prefix = 122)
begin
--Stone of Ice G1
UPDATE [Item] SET Info = (Info|526464) WHERE IID = @iid
end

ELSE IF (@prefix = 123)
begin
--Stone of Ice G2
UPDATE [Item] SET Info = (Info|1050752) WHERE IID = @iid
end

ELSE IF (@prefix = 124)
begin
--Stone of Lightning G1
UPDATE [Item] SET Info = (Info|528512) WHERE IID = @iid
end

ELSE IF (@prefix = 125)
begin
--Stone of Lightning G2
UPDATE [Item] SET Info = (Info|1052800) WHERE IID = @iid
end

ELSE IF (@prefix = 126)
begin
--Stone of Poison G1
UPDATE [Item] SET Info = (Info|532608) WHERE IID = @iid
end

ELSE IF (@prefix = 127)
begin
--Stone of Poison G2
UPDATE [Item] SET Info = (Info|1057016) WHERE IID = @iid
end

ELSE IF (@prefix = 128)
begin
--Stone of Para G1
UPDATE [Item] SET Info = (Info|540910) WHERE IID = @iid
end

ELSE IF (@prefix = 129)
begin
--Stone of Para G2
UPDATE [Item] SET Info = (Info|1065088) WHERE IID = @iid
end

ELSE IF (@prefix = 130)
begin
--Stone of Str G1
UPDATE [Item] SET Info = (Info|557184) WHERE IID = @iid
end

ELSE IF (@prefix = 131)
begin
--Stone of Str G2
UPDATE [Item] SET Info = (Info|1081472) WHERE IID = @iid
end

ELSE IF (@prefix = 132)
begin
--Stone of Skill G1
UPDATE [Item] SET Info = (Info|589952) WHERE IID = @iid
end

ELSE IF (@prefix = 133)
begin
--Stone of Skill G2
UPDATE [Item] SET Info = (Info|1114240) WHERE IID = @iid
end

ELSE IF (@prefix = 134)
begin
--Stone of Mistery G3
UPDATE [Item] SET Info = (Info|1179776) WHERE IID = @iid
end

ELSE IF (@prefix = 135)
begin
--[15]EB
UPDATE [Item] SET UpgrLevel = 15 WHERE IID = @iid
end

ELSE IF (@prefix = 136)
begin
--[10]OTP
UPDATE [Item] SET XHit = 10 WHERE IID = @iid
end

ELSE IF (@prefix = 137)
begin
--[15]Attack
UPDATE [Item] SET XAttack = 15 WHERE IID = @iid
end

ELSE IF (@prefix = 138)
begin
--[15]Magic Attack
UPDATE [Item] SET XMagic = 15 WHERE IID = @iid
end

ELSE IF (@prefix = 139)
begin
--[20]EB [20]Attack [20]OTP
UPDATE [Item] SET UpgrLevel = 20, XHit = 20, XAttack = 20 WHERE IID = @iid
end

ELSE IF (@prefix = 140)
begin
--[15]EB [15]Attack [5]OTP [15]Magic
UPDATE [Item] SET UpgrLevel = 15, XHit = 5, XAttack = 15, XMagic = 15, Prefix = 100 WHERE IID = @iid
end

ELSE IF (@prefix = 141)
begin
--Guild Time Reset
UPDATE [GuildMember] SET Date = 0 WHERE PID = @pid
end

ELSE IF (@prefix = 142)
begin
--20/20/20/20 Red Claw
UPDATE [Item] SET UpgrLevel = 20, XHit = 20, XAttack = 20, Prefix = 101 WHERE IID = @iid
end

ELSE IF (@prefix = 143)
begin
--Eb success chance 100% - UpgradeRate -> 100
UPDATE [Item] SET UpgrRate = 100 WHERE IID = @iid
end

ELSE IF (@prefix = 144)
begin
--5 Def / 1 Eva[Armor] -> Players
UPDATE [Item] SET XDefense = 5, XDodge = 1 WHERE IID = @iid
end

ELSE IF (@prefix = 145)
begin
--10 Def / 1Eva[Armor] -> Gm's
UPDATE [Item] SET XDefense = 10, XDodge = 1 WHERE IID = @iid
end

ELSE IF (@prefix = 146)
begin
--10 Def / 1Eva / Red Claw / Db Mix G3[Armor] -> Gm's (kann bei zu viel parts probleme verursachen GLAUBE ich)
UPDATE [Item] SET XDefense = 10, XDodge = 1, Prefix = **** Info = (Info|1310720) WHERE IID = @iid
end

ELSE IF (@prefix = 147)
begin
--20/20/255 Darkchaos - Pamz
UPDATE [Item] SET UpgrLevel = 255, XHit = 20, XAttack = 20, Prefix = 100 WHERE IID = @iid
end

ELSE IF (@prefix = 148)
begin
if(@bof_index > 1)
SET @bofindex1 = @bof_index
else
SET @bofindex1 = @index

UPDATE Item SET [Index] = @bofindex1 WHERE [IID] = @iid
end

ELSE
begin
--Do the normal query
UPDATE Item SET [Prefix] = @prefix WHERE [IID] = @iid
end
GO
    Reply With Quote Reply With Quote
  4. 20-01-10 #3
    rimidalv
    Enthusiast rimidalv is offline
    MemberRank
    Dec 2006 Join Date
    36Posts

    Re: Stored Procedure for SQL 2000

    Quote Originally Posted by KalEssence View Post
    Hey all!
    I found this procedure witch one is for SQL 2005. I tested it on the SQL 2000, dosen't work. Can somebody tell me what should i fix, cuz I need it on SQL 2000 =(( and the things would be nice for me.
    Oryginal post is here:

    Thx, w8ing for help :*****
    http://www.gblprotect.net/showthread...ored+Procedure

    Code:
    USE [kal_db]
GO
/****** Object: StoredProcedure [dbo].[SetPrefix] Script Date: 08/29/2009 13:00:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SetPrefix]
 
@prefix int,
@iid int
 
AS
 
 
declare @pid int
declare @index int
declare @bof_index int
declare @bofindex1 int
SET @pid = (SELECT PID FROM Item WHERE [IID] = @iid)
SET @index = (SELECT [Index] FROM [Item] WHERE [IID] = @iid)
SET @bof_index = (SELECT [BofIndex] FROM [BofTable] WHERE [Index] = @index)
 
IF (@prefix = 113)
begin
--do a special thing, like make bound
UPDATE [Item] SET Info = (Info|128) WHERE IID = @iid
end
 
ELSE IF (@prefix = 114)
begin
--do a special thing, like make unbound
UPDATE [Item] SET Info = Info - (Info&128) WHERE IID = @iid
end
 
ELSE IF (@prefix = 115)
begin
--Stone of Demon Blood G3
UPDATE [Item] SET Info = (Info|1310912) WHERE IID = @iid
end
 
ELSE IF (@prefix = 116)
begin
--Stone of Shadow G1
UPDATE [Item] SET Info = (Info|524672) WHERE IID = @iid
end
 
ELSE IF (@prefix = 117)
begin
--Stone of Shadow G2
UPDATE [Item] SET Info = (Info|1048960) WHERE IID = @iid
end
 
ELSE IF (@prefix = 118)
begin
--Stone of Holy G1
UPDATE [Item] SET Info = (Info|524928) WHERE IID = @iid
end
 
ELSE IF (@prefix = 119)
begin
--Stone of Holy G2
UPDATE [Item] SET Info = (Info|1049216) WHERE IID = @iid
end
 
ELSE IF (@prefix = 120)
begin
--Stone of Flame G1
UPDATE [Item] SET Info = (Info|525440) WHERE IID = @iid
end
 
ELSE IF (@prefix = 121)
begin
--Stone of Flame G2
UPDATE [Item] SET Info = (Info|1049728) WHERE IID = @iid
end
 
ELSE IF (@prefix = 122)
begin
--Stone of Ice G1
UPDATE [Item] SET Info = (Info|526464) WHERE IID = @iid
end
 
ELSE IF (@prefix = 123)
begin
--Stone of Ice G2
UPDATE [Item] SET Info = (Info|1050752) WHERE IID = @iid
end
 
ELSE IF (@prefix = 124)
begin
--Stone of Lightning G1
UPDATE [Item] SET Info = (Info|528512) WHERE IID = @iid
end
 
ELSE IF (@prefix = 125)
begin
--Stone of Lightning G2
UPDATE [Item] SET Info = (Info|1052800) WHERE IID = @iid
end
 
ELSE IF (@prefix = 126)
begin
--Stone of Poison G1
UPDATE [Item] SET Info = (Info|532608) WHERE IID = @iid
end
 
ELSE IF (@prefix = 127)
begin
--Stone of Poison G2
UPDATE [Item] SET Info = (Info|1057016) WHERE IID = @iid
end
 
ELSE IF (@prefix = 128)
begin
--Stone of Para G1
UPDATE [Item] SET Info = (Info|540910) WHERE IID = @iid
end
 
ELSE IF (@prefix = 129)
begin
--Stone of Para G2
UPDATE [Item] SET Info = (Info|1065088) WHERE IID = @iid
end
 
ELSE IF (@prefix = 130)
begin
--Stone of Str G1
UPDATE [Item] SET Info = (Info|557184) WHERE IID = @iid
end
 
ELSE IF (@prefix = 131)
begin
--Stone of Str G2
UPDATE [Item] SET Info = (Info|1081472) WHERE IID = @iid
end
 
ELSE IF (@prefix = 132)
begin
--Stone of Skill G1
UPDATE [Item] SET Info = (Info|589952) WHERE IID = @iid
end
 
ELSE IF (@prefix = 133)
begin
--Stone of Skill G2
UPDATE [Item] SET Info = (Info|1114240) WHERE IID = @iid
end
 
ELSE IF (@prefix = 134)
begin
--Stone of Mistery G3
UPDATE [Item] SET Info = (Info|1179776) WHERE IID = @iid
end
 
ELSE IF (@prefix = 135)
begin
--[15]EB
UPDATE [Item] SET UpgrLevel = 15 WHERE IID = @iid
end
 
ELSE IF (@prefix = 136)
begin
--[10]OTP
UPDATE [Item] SET XHit = 10 WHERE IID = @iid
end
 
ELSE IF (@prefix = 137)
begin
--[15]Attack
UPDATE [Item] SET XAttack = 15 WHERE IID = @iid
end
 
ELSE IF (@prefix = 138)
begin
--[15]Magic Attack
UPDATE [Item] SET XMagic = 15 WHERE IID = @iid
end
 
ELSE IF (@prefix = 139)
begin
--[20]EB [20]Attack [20]OTP
UPDATE [Item] SET UpgrLevel = 20, XHit = 20, XAttack = 20 WHERE IID = @iid
end
 
ELSE IF (@prefix = 140)
begin
--[15]EB [15]Attack [5]OTP [15]Magic
UPDATE [Item] SET UpgrLevel = 15, XHit = 5, XAttack = 15, XMagic = 15, Prefix = 100 WHERE IID = @iid
end
 
ELSE IF (@prefix = 141)
begin
--Guild Time Reset
UPDATE [GuildMember] SET Date = 0 WHERE PID = @pid
end
 
ELSE IF (@prefix = 142)
begin
--20/20/20/20 Red Claw
UPDATE [Item] SET UpgrLevel = 20, XHit = 20, XAttack = 20, Prefix = 101 WHERE IID = @iid
end
 
ELSE IF (@prefix = 143)
begin
--Eb success chance 100% - UpgradeRate -> 100
UPDATE [Item] SET UpgrRate = 100 WHERE IID = @iid
end
 
ELSE IF (@prefix = 144)
begin
--5 Def / 1 Eva[Armor] -> Players
UPDATE [Item] SET XDefense = 5, XDodge = 1 WHERE IID = @iid
end
 
ELSE IF (@prefix = 145)
begin
--10 Def / 1Eva[Armor] -> Gm's
UPDATE [Item] SET XDefense = 10, XDodge = 1 WHERE IID = @iid
end
 
ELSE IF (@prefix = 146)
begin
--10 Def / 1Eva / Red Claw / Db Mix G3[Armor] -> Gm's (kann bei zu viel parts probleme verursachen GLAUBE ich)
UPDATE [Item] SET XDefense = 10, XDodge = 1, Prefix = **** Info = (Info|1310720) WHERE IID = @iid
end
 
ELSE IF (@prefix = 147)
begin
--20/20/255 Darkchaos - Pamz
UPDATE [Item] SET UpgrLevel = 255, XHit = 20, XAttack = 20, Prefix = 100 WHERE IID = @iid
end
 
ELSE IF (@prefix = 148)
begin
if(@bof_index > 1)
SET @bofindex1 = @bof_index
else
SET @bofindex1 = @index
 
UPDATE Item SET [Index] = @bofindex1 WHERE [IID] = @iid
end
 
ELSE
begin
--Do the normal query
UPDATE Item SET [Prefix] = @prefix WHERE [IID] = @iid
end

    change

    ALTER PROCEDURE [dbo].[SetPrefix]

    to

    CREATE PROCEDURE [dbo].[SetPrefix]

    (1st posted by Bjorn)
    Reply With Quote Reply With Quote
  5. 15-03-11 #4
    cobra_amm7
    Enthusiast cobra_amm7 is offline
    MemberRank
    Nov 2006 Join Date
    36Posts

    Re: Stored Procedure for SQL 2000

    Hi...

    I did everything you say here step by step, to bad it's not working,can you help me,

    I tried to use the Demon's Blood Stone and it's only put 155 in the PREFIX, the INFO doen't changed, It still ( 0 )

    this is what I have for the DB stone
    ELSE IF (@prefix = 115)
    begin
    --Stone of Demon Blood G3
    UPDATE [Item] SET Info = (Info|1310912) WHERE IID = @iid
    end

    Exactully like yours, so is there anyone can help please, and thank you
    Reply With Quote Reply With Quote


« Previous Thread | Next Thread »

Advertisement