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

[Royalblade*]

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

- Shitty mention is annoying. So fuck this shit into pastebin.

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

[Yui..]

+1

[EdwardTeach+-]

professional as always man

[Syloxx]

hmm Akasch, bin besseres von dir gewohnt...

meine query ist sehr viel kürzer und auch schneller

[AdventureOnline]

+11

[Royalblade*]

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 ****. Crappy enough to be released on epvp aint it?

Originally Posted by Royalblade*

Oh by the way, this **** 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.

[Syloxx]

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 ****. Crappy enough to be released on epvp aint it?

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

[PlantiX]

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?

[Bocc1337]

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


if (@Operation = '16' and @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 = @D and Slot= 13 and ItemID>0)
end


Have fun so where my 100 dollers

[sarkoplata]

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 @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 = @D and Slot= 13 and ItemID>0)
end


Have fun so where my 100 dollers

feelsbadman

[Syloxx]

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 @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 = @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

[Bocc1337]

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..

[Royalblade*]

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 @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 = @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.

[WickedNite.]

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 ****. Crappy enough to be released on epvp aint it?

Wait for that BR server with no edits.

[Bocc1337]

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. _|_