Clean and functional Stored Procedures

04/19/2019 04:25 *Silverlight*#1
What the hell is this?

While working on checking the Stored Procedures for blietzkrieg (or however it's written) package I noticed that many of them will not run due to wrong column names being used or more weird scenarios.

I have basically gone through each one of the errors and manually fixed the references so that stuff like SMC doesn't crash like crazy on you anymore.

The fixed Stored Procedures seem to be related with automatic content creation which could be related to a tool we don't have access to by Joymax. But some of them have to do with Stall Network (the ItemQuotation creation Stored Procedure is messed up by default, and I'm not 100% sure that my version will fix any ItemQuotation issues if any).

How to use this?

Open your SQL Management Studio, open a Query after selecting your SRO_VT_SHARD, and place the text in the attached file inside. Run the query with F5 and see how your Stored Procedures get reapplied with no errors.

How can I know if my Stored Procedures are screwed?

Do this if you do not want to run my queries with blindfold and want to know if your database has issues by yourself. Follow these steps to reproduce the way that I found that the SPs were screwed:
  1. Enter your SQL Management Studio.
  2. Right click on your SRO_VT_SHARD (or equivalent).
  3. Select Tasks/Generate Scripts.
  4. Click on 'Next' or 'Choose Objects' in the sidebar.
  5. Select 'Select specific database objects'.
  6. Mark the 'Stored Procedures' checkbox and hit Next.
  7. Click on 'Advanced' and change the 'Script DROP and CREATE' option to 'Script DROP and CREATE' instead of 'Script CREATE'.
  8. Click on 'Next' twice to generate the scripts with your Stored Procedures.
  9. Go to your 'Documents' and open 'script.sql' with Miscrosoft SQL Management Studio.
  10. Select SRO_VT_SHARD and run the query.
  11. Check any errors in the output to verify if your SPs are clean or not.
  12. Fix any errors and keep running the query until everything in your output is black.

Are my other databases good or not?

I just thought about checking the other databases, but I'm too lazy to do it right now. Might go ahead and do it later on, but anyone else is welcome to reproduce the steps above to check their other databases.

I recommend you to check your Stored Procedures manually if you are a capable developer, since I am not the smartest one out there. This could come with new bugs that nobody ever saw before, so take care with my releases jeje.
04/21/2019 19:26 Klito*#2
Nice, Potato!
04/25/2019 01:01 thebigbody#3
that's amazing actually and will make the db more stable but you have 1 mistake in Proc "_InsertSiegeFortressStruct"
you changed the column name from GameConfig1 to WorldCodeName128 which is calling the name from table _RefSiegeFortress and in this case it won't be the same ever cuz they are different
the meant column here instead of GameConfig1 should be ConfigGroupCodeName128 but it will need a little change in its structure because they are not the same as well the return still will be 0
so i made it suitable for both proc and column values and it will be something like this

PHP Code:
USE SRO_VT_SHARD
GO
/****** Object:  StoredProcedure [dbo].[_InsertSiegeFortressStruct]    Script Date: 4/25/2019 12:58:22 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[_InsertSiegeFortressStruct]
    @FortressID        INT
AS
    DECLARE @RefEventStructID    INT
    DECLARE @StructCodeName        VARCHAR(129)
    DECLARE @FortressName        VARCHAR(129)
    
    IF @FortressID <= 0
    BEGIN
        RETURN -1
    END

    SELECT @FortressName = CodeName128 FROM _RefSiegeFortress WITH (NOLOCK) WHERE FortressID = @FortressID

    DECLARE FortressStructCursor CURSOR  FOR
    SELECT [ID],strParam1  FROM _RefEventZone WITH (NOLOCK) WHERE Service = 1 AND strParam2 = (
        SELECT WorldCodeName128 FROM _RefGame_World WHERE ConfigGroupCodeName128 = 'GROUP_'+@FortressName
    )

    OPEN FortressStructCursor
    FETCH NEXT FROM FortressStructCursor INTO @RefEventStructID, @StructCodeName
    WHILE (@@fetch_status = 0)
    BEGIN    
        DECLARE @RefObjID       INT
        DECLARE @Link    INT
        
        SET @RefObjID = 0
        SET @Link = 0

        SELECT @RefObjID = [ID], @Link = Link FROM _RefObjCommon WITH (NOLOCK) WHERE CodeName128 = @StructCodeName
        IF @RefObjID <= 0
        BEGIN
            FETCH NEXT FROM FortressStructCursor INTO @RefEventStructID, @StructCodeName
            CONTINUE
        END

        DECLARE @MaxHP   INT
        SELECT @MaxHP = MaxHP FROM _RefObjChar WITH (NOLOCK) WHERE [ID] = @Link
        IF @MaxHP < 0
        BEGIN
            FETCH NEXT FROM FortressStructCursor INTO @RefEventStructID, @StructCodeName
            CONTINUE
        END

        IF NOT EXISTS (SELECT * FROM _SiegeFortressStruct  WITH (NOLOCK)  WHERE RefEventStructID = @RefEventStructID)
        BEGIN
            -- ·¹ÆÛ·±½º Å×ÀÌºí¿¡¼* Æ÷Æ®¸®½º ÀνºÅϽº¸¦ ¹Ì¸® ¸¸µç´Ù. °ªÀº ¸ðµÎ default·Î ¼¼ÆÃ!
            INSERT INTO [dbo]._SiegeFortressStruct VALUES ( @FortressID, 0, @RefEventStructID, @RefObjID, @MaxHP, GetDate(), 0)
        END

        FETCH NEXT FROM FortressStructCursor INTO @RefEventStructID, @StructCodeName
    END

    CLOSE FortressStructCursor
    DEALLOCATE FortressStructCursor    

    RETURN 0 
05/01/2019 03:17 anikan1985#4
Cannot resolve the collation conflict between "Korean_Wansung_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
05/01/2019 05:06 JohnDornel^#5
Quote:
Originally Posted by anikan1985 View Post
Cannot resolve the collation conflict between "Korean_Wansung_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Find on which line it happens, google on how to use COLLATE (or you can just try to randomly place it xD).

COLLATE DATABASE_DEFAULT
05/01/2019 23:56 anikan1985#6
well its not only 1 error :

[Only registered and activated users can see links. Click Here To Register...]
05/03/2019 12:47 Dev.L0RdReVaN#7
WOW
that's awesome
05/03/2019 15:49 thebigbody#8
Quote:
Originally Posted by anikan1985 View Post
well its not only 1 error :

[Only registered and activated users can see links. Click Here To Register...]
it's all about ID column just make it auto in all of em
08/20/2019 22:17 ScarOnline#9
awesome release, thanks for sharing