[RELEASE] Query to create a new non-player character (NPC) + shop

[Syloxx]

Hello,

i decided to release a part of my "create a new non-player character" query

till now i was too lazy to create comments and print messages and i also removed "create npc tab", "create npc group", "item mall support", "client support (export client files from sql)"

anyway here we go

/**
version : 1
author : syloxx
created date : 2015-07-30
description : Create a new non-player character, shop tab or shop group.
**/

USE SRO_VT_SHARD

SET NOCOUNT ON
SET XACT_ABORT ON

DECLARE @vcPrintMessage varchar(1024)
----------------------------------------------------------------------------------------------------
--  , @inyType tinyint = 1 /*_# */
    , @vcShopCodeName varchar(128) = 'CH_SMITH' /*_# */
    , @vcShopModel varchar(512) = 'npc\npc\EastEuropeSystem_HunterUnion.bsr' /*_# */
    , @vcShopFortress varchar(32) = 'FORTRESS_JANGAN' /*_# */
    , @vcTargetCharName varchar(64) = 'Syloxx' /*_# */
    , @insRotation smallint = 90 /*_# */
    , @intGameWorldID int = 1 /*_# */
    , @vcShopTabCodeName varchar(32) = 'WEAPON' /*_# */
--  , @inyShopGroupID tinyint = 1 /*_# */
----------------------------------------------------------------------------------------------------

/*_# */
IF XACT_STATE() = -1
BEGIN
    SET @vcPrintMessage = ''
    GOTO ErrorHandler
END

BEGIN TRY
    /*_# */
--  IF @inyType = 1
--  BEGIN
        DECLARE @insRegionID smallint
            , @rlPosX real
            , @rlPosY real
            , @rlPosZ real
            , @intObjLink int
            , @intObjID int
            , @intTacticsID int
            , @intHiveID int
        
        /*_# */
        IF EXISTS (SELECT * FROM _RefObjCommon WHERE CodeName128 = 'NPC_' + @vcShopCodeName)
        BEGIN
            SET @vcPrintMessage = '1'
            GOTO ErrorHandler
        END
        
        /*_# */
        IF @vcShopModel NOT LIKE '%.bsr'
        BEGIN
            SET @vcPrintMessage = '2'
            GOTO ErrorHandler
        END
        
        /*_# */
        IF @vcShopFortress NOT IN ('FORTRESS_BIJEOKDAN', 'FORTRESS_CONSTANTINOPLE', 'FORTRESS_DONWHANG', 'FORTRESS_EVILORDER', 'FORTRESS_HEUKMAKDAN', 'FORTRESS_HOTAN', 'FORTRESS_JANGAN', 'FORTRESS_SAMARKAND', 'xxx')
        BEGIN
            SET @vcPrintMessage = '3'
            GOTO ErrorHandler
        END
        
        /*_# */
        IF @insRotation NOT BETWEEN 0 AND 360
        BEGIN
            SET @vcPrintMessage = '4'
            GOTO ErrorHandler
        END
        
        /*_# */
        IF @intGameWorldID NOT IN (SELECT ID FROM _RefGame_World)
        BEGIN
            SET @vcPrintMessage = '5'
            GOTO ErrorHandler
        END
        
        /*_# */
        SELECT @insRegionID = LatestRegion
            , @rlPosX = PosX
            , @rlPosY = PosY
            , @rlPosZ = PosZ
        FROM dbo._Char
        WHERE CharName16 = @vcTargetCharName
        
        /*_# */
        IF @insRegionID IS NULL
        BEGIN
            SET @vcPrintMessage = '6'
            GOTO ErrorHandler
        END
        
        BEGIN TRANSACTION
        
        /*_# */
        INSERT dbo._RefObjChar
        VALUES (0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 336860180, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
            SET @intObjLink = SCOPE_IDENTITY()
        
        /*_# */
        INSERT dbo._RefObjCommon
        VALUES (1, 'NPC_' + @vcShopCodeName, 'xxx', 'xxx', 'SN_NPC_' + @vcShopCodeName, 'xxx', 0, 1, 1, 2, 2, 0, 5000, 3, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, -1, 0, -1, 0, -1, 0, -1, 0, -1, 0, 0, 0, 0, 0, 0, 0, 100, 0, 0, 0, @vcShopModel, 'xxx', 'xxx', @vcShopFortress, 'xxx', @intObjLink)
            SET @intObjID = SCOPE_IDENTITY()
        
        /*_# */
        INSERT dbo._RefShop
        VALUES (1, 15, 'STORE_' + @vcShopCodeName, -1, 'xxx', -1, 'xxx', -1, 'xxx', -1, 'xxx')
        
        /*_# */
        INSERT dbo._RefShopGroup
        VALUES (1, 15, 'GROUP_STORE_' + @vcShopCodeName, 'NPC_' + @vcShopCodeName, -1, 'xxx', -1, 'xxx', -1, 'xxx', -1, 'xxx')
        
        /*_# */
        INSERT dbo._RefShopItemGroup
        VALUES (1, 'STORE_' + @vcShopCodeName + '_GROUP1', 'SN_STORE_' + @vcShopCodeName + '_GROUP1')
        
        /*_# */
        INSERT dbo._RefShopTab
        VALUES (1, 15, 'STORE_' + @vcShopCodeName + '_TAB1', 'STORE_' + @vcShopCodeName + '_GROUP1', 'SN_TAB_' + @vcShopTabCodeName)
        
        /*_# */
        INSERT dbo._RefShopTabGroup
        VALUES (1, 15, 'STORE_' + @vcShopCodeName + '_GROUP1', 'SN_STORE_' + @vcShopCodeName + '_GROUP1')
        
        /*_# */
        INSERT dbo._RefMappingShopGroup
        VALUES (1, 15, 'GROUP_STORE_' + @vcShopCodeName, 'STORE_' + @vcShopCodeName)
        
        /*_# */
        INSERT dbo._RefMappingShopWithTab
        VALUES (1, 15, 'STORE_' + @vcShopCodeName, 'STORE_' + @vcShopCodeName + '_GROUP1')
        
        /*_# */
        SELECT @intTacticsID = MAX(dwTacticsID) + 10
        FROM dbo.Tab_RefTactics
        
        /*_# */
        INSERT dbo.Tab_RefTactics
        VALUES (@intTacticsID, @intObjID, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 'NPC_' + @vcShopCodeName)
        
        /*_# */
        INSERT dbo.Tab_RefHive
        VALUES (0, 0, 0, 0, 0, 0, @intGameWorldID, 2, 'NPC_' + @vcShopCodeName)
            SET @intHiveID = SCOPE_IDENTITY()
        
        /*_# */
        INSERT dbo.Tab_RefNest
        VALUES (@intHiveID, @intTacticsID, @insRegionID, @rlPosX, @rlPosY, @rlPosZ, CEILING(POWER(2, 15) * (-1) + ((CONVERT(float, POWER(2, 16) - 1) / 360) * ((@insRotation + 90) % 360))), 0, 0, 0, 0, 0, 1, 0, 1, 0)

        COMMIT TRANSACTION
--  END
END TRY
BEGIN CATCH
    GOTO ErrorHandler
END CATCH

PRINT '0'
RETURN

ErrorHandler:
IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION

PRINT @vcPrintMessage
RETURN

if you guys want the full query or similar queries you can find my service in my signature

[Eslam Galull]

Explain & some screens ! would be great

[B1Q B0SS]

non-player character (NPC)

y u know too match

Originally Posted by Syloxx

if you guys want the full query or similar queries you can find my service in my signature

you support night services? no ****

[Syloxx]

Originally Posted by its.soul

Explain & some screens ! would be great

its actually self-explanatory

[tohamey]

Amazing work clean and tested it already with Syloxx on my private server
Clean, easy to read even for beginners like myself

[Eslam Galull]

Originally Posted by Syloxx

its actually self-explanatory

yea i see

[synsroadmin]

thanks about NPC query

[XxGhostSpiriTxX]

Mens. 213, Level 16, State 1, Line 147
The column name or the specified values do not correspond to the definition of the table.

pliss help

[Syloxx]

Originally Posted by esteve458

Mens. 213, Level 16, State 1, Line 147
The column name or the specified values do not correspond to the definition of the table.

pliss help

looks like your DB have modified tables so i wont support you

don't modify Joymax tables and you are just fine