[RELEASE]Skalidor's Remove RedStats/Blues from Items

Page 1 of 2

03/01/2016 19:53 Royalblade*#1

I heard some people are selling this shit for 100$. So I'm releasing this just to fuck around with them.

So this shit removes any kind of blue from an item, that you want removed for whatever reason. Currently its designed to remove red stats but it can be changed to anything by changing BETWEEN 1 - 4 if I remember correctly.
Been over 3 years now that I have this shit. Time to get rid of it.

Oh by the way, this shit is slow as hell probably since it was written at the beginning of my so-called sro career.
There's no proper error handling, transactions or anything. Just pure bullshit. I know, I could've done this a lot easier by STUFF-ing or PIVOT-ing, for example. However, 3 yrs old, what do ya expect.

PS: I'm not releasing the table _RefMagicOptNew; Either you make your own or use statics for the blues.
I've released a formula to calculate blues static somewhere on epvp or RZ before. Don't remember where that shit was. However currently its as dynamic as it can be for various blues.

HF.

- The beloved Royalblade

Spoiler

[Only registered and activated users can see links. Click Here To Register...] - Shitty mention is annoying. So fuck this shit into pastebin.

Code:

USE [SRO_VT_ROYAL]
GO
/****** Object:  StoredProcedure [dbo].[_RedStatRemover]    Script Date: 03/07/2016 10:36:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[_RedStatRemover]
  [MENTION=395373]Chari[/MENTION]D INT

AS

        DECLARE          [MENTION=1985011]param[/MENTION]1 BIGINT,
                         [MENTION=1985011]param[/MENTION]2 BIGINT,
                         [MENTION=1985011]param[/MENTION]3 BIGINT,
                         [MENTION=1985011]param[/MENTION]4 BIGINT,
                         [MENTION=1985011]param[/MENTION]5 BIGINT,
                         [MENTION=1985011]param[/MENTION]6 BIGINT,
                         [MENTION=1985011]param[/MENTION]7 BIGINT,
                         [MENTION=1985011]param[/MENTION]8 BIGINT,
                         [MENTION=1985011]param[/MENTION]9 BIGINT,
                         [MENTION=1985011]param[/MENTION]10 BIGINT,
                         [MENTION=1985011]param[/MENTION]Num TINYINT,
                         [MENTION=806297]sql[/MENTION] nvarchar(400),
                         [MENTION=783256]redsta[/MENTION]t TINYINT,
                        @ID64 INT;

        SELECT           [MENTION=1985011]param[/MENTION]1 = magic1.ID ,
                         [MENTION=1985011]param[/MENTION]2 = magic2.ID ,
                         [MENTION=1985011]param[/MENTION]3 = magic3.ID ,
                         [MENTION=1985011]param[/MENTION]4 = magic4.ID ,
                         [MENTION=1985011]param[/MENTION]5 = magic5.ID ,
                         [MENTION=1985011]param[/MENTION]6 = magic6.ID ,
                         [MENTION=1985011]param[/MENTION]7 = magic7.ID ,
                         [MENTION=1985011]param[/MENTION]8 = magic8.ID ,
                         [MENTION=1985011]param[/MENTION]9 = magic9.ID ,
                         [MENTION=1985011]param[/MENTION]10 = magic10.ID,
                         [MENTION=1985011]param[/MENTION]Num = items0.MagParamNum,
                        @ID64 = items0.ID64

        FROM            SRO_VT_SHARD_INIT.dbo._Inventory as invent0
        INNER JOIN      SRO_VT_SHARD_INIT.dbo._Items as items0 on invent0.ItemID = items0.ID64 AND (invent0.Slot  = 13)
        INNER JOIN      SRO_VT_SHARD_INIT.dbo._RefObjCommon as ref0 on items0.RefItemID = ref0.ID
        INNER JOIN      SRO_VT_SHARD_INIT.dbo._Char as chart on invent0.CharID = chart.CharID
        LEFT JOIN       _RefMagicOptNew as magic1 on (items0.MagParam1 & 2147483647) = magic1.ID
        LEFT JOIN       _RefMagicOptNew as magic2 on (items0.MagParam2 & 2147483647) = magic2.ID
        LEFT JOIN       _RefMagicOptNew as magic3 on (items0.MagParam3 & 2147483647) = magic3.ID
        LEFT JOIN       _RefMagicOptNew as magic4 on (items0.MagParam4 & 2147483647) = magic4.ID
        LEFT JOIN       _RefMagicOptNew as magic5 on (items0.MagParam5 & 2147483647) = magic5.ID
        LEFT JOIN       _RefMagicOptNew as magic6 on (items0.MagParam6 & 2147483647) = magic6.ID
        LEFT JOIN       _RefMagicOptNew as magic7 on (items0.MagParam7 & 2147483647) = magic7.ID
        LEFT JOIN       _RefMagicOptNew as magic8 on (items0.MagParam8 & 2147483647) = magic8.ID
        LEFT JOIN       _RefMagicOptNew as magic9 on (items0.MagParam9 & 2147483647) = magic9.ID
        LEFT JOIN       _RefMagicOptNew as magic10 on (items0.MagParam10 & 2147483647) = magic10.ID
        LEFT JOIN       _RefMagicOptNew as magic11 on (items0.MagParam11 & 2147483647) = magic11.ID
        LEFT JOIN       _RefMagicOptNew as magic12 on (items0.MagParam12 & 2147483647) = magic12.ID

        WHERE chart.CharID =  [MENTION=395373]Chari[/MENTION]D

        SET  [MENTION=783256]redsta[/MENTION]t = (SELECT CASE WHEN  [MENTION=1985011]param[/MENTION]1 BETWEEN 1 AND 4
                                        THEN 1
                                    WHEN  [MENTION=1985011]param[/MENTION]2 BETWEEN 1 AND 4
                                        THEN 2
                                    WHEN  [MENTION=1985011]param[/MENTION]3 BETWEEN 1 AND 4
                                        THEN 3
                                    WHEN  [MENTION=1985011]param[/MENTION]4 BETWEEN 1 AND 4
                                        THEN 4
                                    WHEN  [MENTION=1985011]param[/MENTION]5 BETWEEN 1 AND 4
                                        THEN 5
                                    WHEN  [MENTION=1985011]param[/MENTION]6 BETWEEN 1 AND 4
                                        THEN 6
                                    WHEN  [MENTION=1985011]param[/MENTION]7 BETWEEN 1 AND 4
                                        THEN 7
                                    WHEN  [MENTION=1985011]param[/MENTION]8 BETWEEN 1 AND 4
                                        THEN 8
                                    WHEN  [MENTION=1985011]param[/MENTION]9 BETWEEN 1 AND 4
                                        THEN 9
                                    WHEN  [MENTION=1985011]param[/MENTION]10 BETWEEN 1 AND 4
                                        THEN 10
                                    ELSE null
                                END )
                                
            
        IF  [MENTION=783256]redsta[/MENTION]t <  [MENTION=1985011]param[/MENTION]Num
            BEGIN
                SET  [MENTION=806297]sql[/MENTION] = ' UPDATE     SRO_VT_SHARD_INIT.dbo._Items
                             SET        MagParam' + CAST  [MENTION=783256]redsta[/MENTION]t as nvarchar(2)) + ' = MagParam' + CAST  [MENTION=1985011]param[/MENTION]Num as nvarchar(2)) + ',
                                        MagParam' + CAST  [MENTION=1985011]param[/MENTION]Num as nvarchar(2)) + ' = MagParam' + CAST  [MENTION=783256]redsta[/MENTION]t as nvarchar(2)) + ',
                                        MagParamNum -= 1
                             WHERE      ID64 = ' + CAST(@ID64 as nvarchar(10))
                             
                EXEC sp_executeSQL  [MENTION=806297]sql[/MENTION], N  [MENTION=783256]redsta[/MENTION]t TINYINT,  [MENTION=1985011]param[/MENTION]Num TINYINT, @ID64 INT',  [MENTION=783256]redsta[/MENTION]t,  [MENTION=1985011]param[/MENTION]Num, @ID64 
                Return -3
            END
        ELSE IF  [MENTION=783256]redsta[/MENTION]t =  [MENTION=1985011]param[/MENTION]Num
            BEGIN
                UPDATE      SRO_VT_SHARD_INIT.dbo._Items 
                SET         MagParamNum -= 1
                WHERE       ID64 = @ID64
                RETURN -1
            END
        ELSE
            BEGIN
                DECLARE  [MENTION=3898309]charname[/MENTION] varchar(32) = (SELECT Charname16 FROM SRO_VT_SHARD_INIT.dbo._Char WHERE CharID =  [MENTION=395373]Chari[/MENTION]D)
                INSERT INTO _Notification (Type,Target,Content,Executor)
                VALUES (4  [MENTION=3898309]charname[/MENTION], 'Error | Put the Item into Inventory Slot 1 please.',0)
                EXEC SRO_VT_SHARD_INIT.dbo._ADD_ITEM_EXTERN  [MENTION=3898309]charname[/MENTION],'ITEM_SKALIDOR_ANAL_FUCK_REDSTAT',1,0
                Return -2
            END

03/02/2016 01:30 Yui..#2

03/02/2016 09:00 EdwardTeach+-#3

professional as always man

03/02/2016 10:31 Syloxx#4

hmm Akasch, bin besseres von dir gewohnt...

meine query ist sehr viel k�rzer und auch schneller :)

03/02/2016 19:57 AdventureOnline#5

+11

03/03/2016 00:24 Royalblade*#6

Quote:

Originally Posted by Syloxx

hmm Akasch, bin besseres von dir gewohnt...

meine query ist sehr viel k�rzer und auch schneller :)

This shall answer your concern. Additionally, this is dynamic and parts of it come from other queries of mine. To be exact, the ingame item linking query.

Furthermore, it's executed once a day max. I don't see no need to re-write something for performance when it's almost never executed.

However, yes its crap. Crappy enough to be released on epvp aint it?

Quote:

Originally Posted by Royalblade*

Oh by the way, this shit is slow as hell probably since it was written at the beginning of my so-called sro career.
There's no proper error handling, transactions or anything. Just pure bullshit. I know, I could've done this a lot easier by STUFF-ing or PIVOT-ing, for example. However, 3 yrs old, what do ya expect.

03/03/2016 08:14 Syloxx#7

Quote:

Originally Posted by Royalblade*

This shall answer your concern. Additionally, this is dynamic and parts of it come from other queries of mine. To be exact, the ingame item linking query.

Furthermore, it's executed once a day max. I don't see no need to re-write something for performance when it's almost never executed.

However, yes its crap. Crappy enough to be released on epvp aint it?

Pff as I would take effort to read all the stuff you wrote 😂

03/03/2016 08:26 PlantiX#8

Quote:

Originally Posted by Syloxx

hmm Akasch, bin besseres von dir gewohnt...

meine query ist sehr viel k�rzer und auch schneller :)

Nicht gelesen was er dazu geschrieben hat?

03/04/2016 05:59 Bocc1337#9

haha man wtf is that just remove blue from any item? mite want to try this . much much faster..

if (@Operation = '16' and @[Only registered and activated users can see links. Click Here To Register...]Refid ='65001')
begin
Update SRO_VT_SHARD.dbo._Items Set MagParamNum = 0, MagParam1 = NULL, MagParam2 = NULL, MagParam3 = NULL, MagParam4 = NULL, MagParam5 = NULL, MagParam6 = NULL, MagParam7 = NULL, MagParam8 = NULL, MagParam9 = NULL, MagParam10 = NULL, MagParam11 = NULL, MagParam12 = NULL where ID64 in (select ItemID from [SRO_VT_SHARD].[dbo].[_Inventory] where CharID = @[Only registered and activated users can see links. Click Here To Register...]D and Slot= 13 and ItemID>0)
end

Have fun so where my 100 dollers :)

03/04/2016 06:37 sarkoplata#10

Quote:

Originally Posted by sonsof2004

haha man wtf is that just remove blue from any item? mite want to try this . much much faster..

if (@Operation = '16' and @[Only registered and activated users can see links. Click Here To Register...]Refid ='65001')
begin
Update SRO_VT_SHARD.dbo._Items Set MagParamNum = 0, MagParam1 = NULL, MagParam2 = NULL, MagParam3 = NULL, MagParam4 = NULL, MagParam5 = NULL, MagParam6 = NULL, MagParam7 = NULL, MagParam8 = NULL, MagParam9 = NULL, MagParam10 = NULL, MagParam11 = NULL, MagParam12 = NULL where ID64 in (select ItemID from [SRO_VT_SHARD].[dbo].[_Inventory] where CharID = @[Only registered and activated users can see links. Click Here To Register...]D and Slot= 13 and ItemID>0)
end

Have fun so where my 100 dollers :)

feelsbadman

03/04/2016 12:55 Syloxx#11

Quote:

Originally Posted by sonsof2004

haha man wtf is that just remove blue from any item? mite want to try this . much much faster..

if (@Operation = '16' and @[Only registered and activated users can see links. Click Here To Register...]Refid ='65001')
begin
Update SRO_VT_SHARD.dbo._Items Set MagParamNum = 0, MagParam1 = NULL, MagParam2 = NULL, MagParam3 = NULL, MagParam4 = NULL, MagParam5 = NULL, MagParam6 = NULL, MagParam7 = NULL, MagParam8 = NULL, MagParam9 = NULL, MagParam10 = NULL, MagParam11 = NULL, MagParam12 = NULL where ID64 in (select ItemID from [SRO_VT_SHARD].[dbo].[_Inventory] where CharID = @[Only registered and activated users can see links. Click Here To Register...]D and Slot= 13 and ItemID>0)
end

Have fun so where my 100 dollers :)

intelligence quotient: bread

removing all currently magparams from an item isn't hard at all.

but an example

6 STR
6 INT
60% Max Durability Reduce
100% Max Durability Increase

you wanna remove the durability reduce only

good luck doing that with your query ;)

03/05/2016 06:13 Bocc1337#12

Quote:

Originally Posted by Syloxx

intelligence quotient: bread

removing all currently magparams from an item isn't hard at all.

but an example

6 STR
6 INT
60% Max Durability Reduce
100% Max Durability Increase

you wanna remove the durability reduce only

good luck doing that with your query ;)

Durability reduce? This query for taking off blues from any item nothing to do with Durability..

03/05/2016 19:24 Royalblade*#13

Quote:

Originally Posted by sonsof2004

haha man wtf is that just remove blue from any item? mite want to try this . much much faster..

if (@Operation = '16' and @[Only registered and activated users can see links. Click Here To Register...]Refid ='65001')
begin
Update SRO_VT_SHARD.dbo._Items Set MagParamNum = 0, MagParam1 = NULL, MagParam2 = NULL, MagParam3 = NULL, MagParam4 = NULL, MagParam5 = NULL, MagParam6 = NULL, MagParam7 = NULL, MagParam8 = NULL, MagParam9 = NULL, MagParam10 = NULL, MagParam11 = NULL, MagParam12 = NULL where ID64 in (select ItemID from [SRO_VT_SHARD].[dbo].[_Inventory] where CharID = @[Only registered and activated users can see links. Click Here To Register...]D and Slot= 13 and ItemID>0)
end

Have fun so where my 100 dollers :)

Arab idiocy at its best. (definitely no racism intended)
Please, can you just take a deep dive into the nile and remain there for the rest of your pathetic life? Make sure ur germs don't spread man, humanity's biggest challenge is getting rid of you people.

03/05/2016 20:06 WickedNite.#14

Quote:

Originally Posted by Royalblade*

This shall answer your concern. Additionally, this is dynamic and parts of it come from other queries of mine. To be exact, the ingame item linking query.

Furthermore, it's executed once a day max. I don't see no need to re-write something for performance when it's almost never executed.

However, yes its crap. Crappy enough to be released on epvp aint it?

Wait for that BR server with no edits.

03/05/2016 23:36 Bocc1337#15

Quote:

Originally Posted by Royalblade*

Arab idiocy at its best. (definitely no racism intended)
Please, can you just take a deep dive into the nile and remain there for the rest of your pathetic life? Make sure ur germs don't spread man, humanity's biggest challenge is getting rid of you people.

For your info i am a american. Maybe you should check around before you think. _|_

Page 1 of 2