problems with creating stored Procedure mssql

Asked

Viewed 52 times

0

recently I decided to change from using Mysql server database to mssql(sql server version 2012) so far so good what happens is that some things can not remake them

if someone can help me finish the process because where it is " commented " syntax error appears

I put the code that I try to recreate(ORIGINAL CODE)

Here is the google Translate Results:

I recently decided to change database Usage from Mysql server to mssql (sql server version 2012) so far so well what Happens is some Things can’t redo them

if anyone can help me Finish the Procedure because Where is "commented" appears syntax error

I will post the code that I am trying to recreate (ORIGINAL CODE)

    CREATE DEFINER=`root`@`localhost` PROCEDURE `ProcGetMessageMemberInfo_One`(IN @PlayerUID int, IN friend_uid int)
BEGIN
    DECLARE u_id,
            unknown_1,
            unknown_2,
            unknown_3,
            unknown_4,
            unknown_5,
            unknown_6,
            flag_1,
            flag_state,
            player_flag INT(10) DEFAULT 0;
    DECLARE nickname VARCHAR(23);
    DECLARE apelido_in VARCHAR(12);
    DECLARE FINISHED INT DEFAULT 0;

    DECLARE MSN_CURSOR CURSOR FOR
    (   SELECT  a.nick,
                b.uid_friend,
                b.apelido,
                b.unknown1,
                b.unknown2,
                b.unknown3,
                b.unknown4,
                b.unknown5,
                b.unknown6,
                b.flag1,
                b.state_flag,
                @Flag := 1 AS FLAG_PLAYER
        FROM account a, pangya_friend_list b
        WHERE b.uid = @PlayerUID AND a.uid = b.uid_friend AND b.uid_friend = friend_uid
    )
    UNION ALL
    (   SELECT  a.nick,
                @uid_fr := c.member_uid as uid_friend,
                @apel := "" as apelido,
                @unk1 := -1 as unknwon1,
                @unk2 := 0 as unknwon2,
                @unk3 := -1 as unknwon3,
                @unk4 := 0 as unknwon4,
                @unk5 := 0 as unknwon5,
                @unk6 := 0 as unknown6,
                @flag1 := -1 as flag1,
                @flag4 := 0 as state_flag,
                @Flag := 2 AS FLAG_PLAYER
        FROM account a, pangya_guild_member c
        WHERE c.GUILD_UID = (SELECT i.GUILDINDEX FROM Pangya_Member i, pangya_guild_member j WHERE i.UID = @PlayerUID AND i.GUILD_ID = j.GUILD_ID AND j.GUILD_MEMBER_UID = @PlayerUID AND j.GUILD_MEMBER_STATUS < 9)
        AND c.member_uid = a.uid AND c.member_uid = friend_uid AND c.MEMBER_STATE_FLAG != 9
    );

    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET FINISHED = 1;

    DROP TEMPORARY TABLE IF EXISTS `temp_msn_one`;
    CREATE TEMPORARY TABLE `temp_msn_one`(nick VARCHAR(23) NOT NULL,
                                      uid INT(10) NOT NULL,
                                      apelido VARCHAR(12),
                                      unknown1 INT(10),
                                      unknown2 INT(10),
                                      unknown3 INT(10),
                                      unknown4 INT(10),
                                      unknown5 INT(10),
                                      unknown6 INT(10),
                                      flag1 INT(10),
                                      state_flag INT(10),
                                      FLAG_PLAYER INT(10));
    OPEN MSN_CURSOR;

    MSN_LOOP : LOOP
        FETCH MSN_CURSOR INTO nickname, u_id, apelido_in, unknown_1, unknown_2, unknown_3,
                              unknown_4, unknown_5, unknown_6, flag_1, flag_state, player_flag;

        IF FINISHED = 1 THEN
            LEAVE MSN_LOOP;
        END IF;

        IF (SELECT uid FROM temp_msn_one WHERE UID = u_id) IS NOT NULL THEN
            UPDATE temp_msn_one SET FLAG_PLAYER = FLAG_PLAYER + player_flag WHERE UID = u_id;
        ELSE
            INSERT INTO temp_msn_one VALUES(nickname, u_id, apelido_in, unknown_1, unknown_2, unknown_3,
                                        unknown_4, unknown_5, unknown_6, flag_1, flag_state, player_flag);
        END IF;
    END LOOP MSN_LOOP;

    CLOSE MSN_CURSOR;

    SELECT * FROM temp_msn_one;
END

Above code replica in mssql

replica of the code above in mssql

    USE [DB_Pangya]
GO
/****** Object:  StoredProcedure [dbo].[ProcGetMessageMemberInfo_One]    Script Date: 02/08/2019 13:04:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ProcGetMessageMemberInfo_One] 

@PLAYER_UID INT,
 @friend_uid int
AS
BEGIN
   SET NOCOUNT ON;
    DECLARE @u_id INT =0;
    DECLARE @unknown_1 INT =0;
    DECLARE @unknown_2 INT =0;
    DECLARE @unknown_3 INT =0;
    DECLARE @unknown_4 INT =0;
    DECLARE @unknown_5 INT =0;
    DECLARE @unknown_6 INT =0;
    DECLARE @flag_1 INT =0;
    DECLARE @flag_state INT =0;
    DECLARE @player_flag INT = 0;
    DECLARE @nickname VARCHAR(23);
    DECLARE @apelido_in VARCHAR(12);
    DECLARE @FINISHED INT = 0;
    DECLARE @uid_fr INT  =0;
    DECLARE @apel VARCHAR(23) = '';
    DECLARE @unk1 INT =  -1;
    DECLARE @unk2 INT =  0;
    DECLARE @unk3 INT =  -1;
    DECLARE @unk4 INT =  0;
    DECLARE @unk5 INT =  0;
    DECLARE @unk6 INT =  0;
    DECLARE @flag1 INT =  -1;
    DECLARE @flag4 INT =  0;
    DECLARE @Flag INT =  2;

    DECLARE MSN_CURSOR CURSOR FOR
    (   SELECT  a.Nickname,
                b.Friend_UID,
                b.Friend_Nick,
                b.UN,
                b.UN2,
                b.UN3,
                b.UN4,
                b.UN5,
                b.UN6,
                b.flag1,
                b.StatusFlag,
                 1 AS FLAG_PLAYER
        FROM Pangya_Member a, pangya_friend_list b
        WHERE b.uid = @PLAYER_UID AND a.uid = b.Friend_UID AND b.Friend_UID = @friend_uid
    )

     UNION ALL
    (   SELECT  a.Nickname,
                c.GUILD_MEMBER_UID as uid_friend,
                @apel as apelido,
                @unk1  as unknwon1,
                @unk2  as unknwon2,
                @unk3  as unknwon3,
                @unk4 as unknwon4,
                @unk5 as unknwon5,
                @unk6  as unknown6,
                @flag1 as flag1,
                @flag4 as state_flag,
                @Flag  AS FLAG_PLAYER
        FROM pangya_member a, pangya_guild_member c
        WHERE c.GUILD_ID  = (SELECT i.GUILDINDEX FROM Pangya_Member i, pangya_guild_member j WHERE i.UID = @PLAYER_UID AND i.GUILDINDEX = j.GUILD_ID AND j.GUILD_MEMBER_UID = @PLAYER_UID AND j.GUILD_MEMBER_STATUS < 9)
         and c.GUILD_MEMBER_UID = a.uid AND c.GUILD_MEMBER_UID = @friend_uid AND c.GUILD_MEMBER_STATUS != 9
    );

    --DECLARE CONTINUE HANDLER FOR NOT FOUND SET @FINISHED = 1;


   IF (OBJECT_ID('TEMPDB..#temp_msn_one') IS NOT NULL)
     DROP TABLE #temp_msn_one
     create table #temp_msn_one(nick VARCHAR(23) NOT NULL,
                                      uid INT NOT NULL,
                                      apelido VARCHAR(12),
                                      unknown1 INT,
                                      unknown2 INT,
                                      unknown3 INT,
                                      unknown4 INT,
                                      unknown5 INT,
                                      unknown6 INT,
                                      flag1 INT,
                                      state_flag INT,
                                      FLAG_PLAYER INT);

--OPEN MSN_CURSOR;

  --  MSN_LOOP : LOOP
        --FETCH MSN_CURSOR INTO @nickname, @u_id, @apelido_in, @unknown_1, @unknown_2, @unknown_3,
        --                    @unknown_4, @unknown_5, @unknown_6, @flag_1, @flag_state, @player_flag;

  --      IF @FINISHED = 1 begin
        --  LEAVE MSN_LOOP;
        --END

  --      IF (SELECT uid FROM #temp_msn_one WHERE UID = @u_id) IS NOT NULL begin
        --  UPDATE #temp_msn_one SET FLAG_PLAYER = FLAG_PLAYER + @player_flag WHERE UID = @u_id;
        --ELSE
        --  INSERT INTO #temp_msn_one VALUES(@nickname, @u_id, @apelido_in, @unknown_1, @unknown_2, @unknown_3,
        --                    @unknown_4, @unknown_5, @unknown_6, @flag_1, @flag_state, @player_flag);
        --END
  --  END LOOP MSN_LOOP;

    --CLOSE MSN_CURSOR;

    SELECT * FROM #temp_msn_one;

END
sql-server

1 answer

0


Hello replace the part you commented on by this code...I did not create the table to validate, but it will be something there. Validate and give feedback.

    DECLARE MSN_CURSOR CURSOR FOR SELECT * FROM #temp_msn_one;

    OPEN MSN_CURSOR  
    FETCH NEXT FROM MSN_CURSOR INTO @nickname, @u_id, @apelido_in, @unknown_1, @unknown_2, @unknown_3,
                                @unknown_4, @unknown_5, @unknown_6, @flag_1, @flag_state, @player_flag

    WHILE @@FETCH_STATUS = 0  
    BEGIN  
        IF EXISTS (SELECT uid FROM #temp_msn_one WHERE UID = @u_id)
          UPDATE #temp_msn_one SET FLAG_PLAYER = FLAG_PLAYER + @player_flag WHERE UID = @u_id;
        ELSE
            INSERT INTO #temp_msn_one VALUES(@nickname, @u_id, @apelido_in, @unknown_1, @unknown_2, @unknown_3,
                           @unknown_4, @unknown_5, @unknown_6, @flag_1, @flag_state, @player_flag);
        END

        FETCH NEXT FROM MSN_CURSOR INTO @nickname, @u_id, @apelido_in, @unknown_1, @unknown_2, @unknown_3,
                                @unknown_4, @unknown_5, @unknown_6, @flag_1, @flag_state, @player_flag 
    END 

    CLOSE MSN_CURSOR  
    DEALLOCATE MSN_CURSOR

  • i saw that we missed " SELECT * FROM #temp_msn_one; "and "END" to close the code will be if the procedure will be created?

  • this part was also commented, I know that there is a difference between the two languages

  • I’m kind of a layman and I don’t know how to make it work > DECLARE CONTINUE HANDLER FOR NOT FOUND SET @FINISHED = 1; <

  • Do you need the final select for something? I typically when I want to use an SP to create or edit, create an INT = 0 variable, then make SET 1 if update and SET 2 if Insert, and return this variable value.

  • Thank you! technically worked, but there was an error when trying to consult proc

  • You do not need DECLARE... what controls in the loop the end of the cursor is the line "WHILE @@FETCH_STATUS = 0".

  • But the select you mentioned in your first question will need to return is your edited data, I didn’t even look at the logic of what I was doing.......

  • but anyway already helped and I marked the answer as acceptance !

  • when executing the error refers to the cursor, because the already exists a cursor " MSN_CURSOR " right at the beginning of the code and low and declared again and thus causes an error when trying to query

  • You have, the line DECLARE MSN_CURSOR CURSOR FOR SELECT * FROM #temp_msn_one; you can remove.

Show 5 more comments

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.