[SQL] Stored Procedure To add any item to any shop with one or more payment method

[arabianfox]

**This SP add any item to any shop with one or more payment method(s) with just 1 click.

**To execute you just need the Item code name and the shop tab name and item plus (for weapons and sets etc..)

**SP will detect if item and tab is already exist or not..

**For the 8 payment methods every method with 0 value will not used..

**Note that you don't need to edit prices at the bottom with 0 if you set method to 0 ..as SP will ignore price if it's method value = 0

**SP will detect 1st empty slot in the shop tab and get durability of items automatically..

**All media Lines will generated automatically..


You need only to edit this query and execute then copy generated media lines :-

/*                    '     Item Code Name    ','       Shop Tab       ,Item Plus   
*/
   exec [_Any_2_Shop] 'ITEM_CH_BLADE_14_C_RARE','STORE_FOX_Special_TAB5',   8      ,
                      
                      /*Payment Methods*/    -- Methods With Value 0 will not Used
                      
                      1,                   -->  GOLD                 Default Value = 1      --   0 to disable
                      2,                   -->  Silk                 Default Value = 2      --   0 to disable
                      32,                 -->  Honor Point          Default Value = 32     --   0 to disable
                      64,                 -->  Copper Coins         Default Value = 64     --   0 to disable
                      128,               -->  Iron Coins           Default Value = 128    --   0 to disable
                      256,               -->  Silver Coins         Default Value = 256    --   0 to disable
                      512,               -->  Gold Coins           Default Value = 512    --   0 to disable
                      1024,             -->  Arena Coins          Default Value = 1024   --   0 to disable
                      
                      /*Prices List*/    -- If Method value is 0 ,price value of disabled method will not used
                      1000000,           
                      300,
                      500,
                      1000,
                      1000,
                      1000,
                      1000,
                      1000 

Example 1 :-

Spoiler:

Silk and honor points values = 0 So SP will use all prices exept Silk and Honor Points prices .. as in the image

/*                    '     Item Code Name    ','       Shop Tab       ,Item Plus   
*/
   exec [_Any_2_Shop] 'ITEM_CH_BLADE_14_C_RARE','STORE_FOX_Special_TAB5',8,
                      
                      /*Payment Methods*/    -- Methods With Value 0 will not Used
                      
                      1,                 -->  GOLD                 Default Value = 1      --   0 to disable
                      0,                 -->  Silk                 Default Value = 2      --   0 to disable
                      0,                -->  Honor Point          Default Value = 32     --   0 to disable
                      64,                -->  Copper Coins         Default Value = 64     --   0 to disable
                      128,               -->  Iron Coins           Default Value = 128    --   0 to disable
                      256,               -->  Silver Coins         Default Value = 256    --   0 to disable
                      512,               -->  Gold Coins           Default Value = 512    --   0 to disable
                      1024,              -->  Arena Coins          Default Value = 1024   --   0 to disable
                      
                      /*Prices List*/    -- If Method value is 0 ,price value of disabled method will not used
                      10000000,           
                      300,
                      500,
                      1000,
                      1000,
                      1000,
                      1000,
                      1000 

Example 2 :-

Spoiler:

Honor point and Iron coins and arena Coins values = 0 So SP will use gold and silk and copper coins and gold coins prices only..as in the image

/*                    '     Item Code Name    ','       Shop Tab       ,Item Plus   
*/
   exec [_Any_2_Shop] 'ITEM_CH_RING_14_C_RARE','STORE_FOX_Special_TAB5',5,
                      
                      /*Payment Methods*/    -- Methods With Value 0 will not Used
                      
                      1,                 -->  GOLD                 Default Value = 1      --   0 to disable
                      2,                 -->  Silk                 Default Value = 2      --   0 to disable
                      0,                -->  Honor Point          Default Value = 32     --   0 to disable
                      64,                -->  Copper Coins         Default Value = 64     --   0 to disable
                      0,               -->  Iron Coins           Default Value = 128    --   0 to disable
                      256,               -->  Silver Coins         Default Value = 256    --   0 to disable
                      512,               -->  Gold Coins           Default Value = 512    --   0 to disable
                      0,              -->  Arena Coins          Default Value = 1024   --   0 to disable
                      
                      /*Prices List*/    -- If Method value is 0 ,price value of disabled method will not used
                      10000000,           
                      300,
                      500,
                      1000,
                      1000,
                      1000,
                      1000,
                      1000 

i hope this be helpful for someone..

[MentaL]

[silkroadgamer]

Nic3 release!! Thanks Pro! :)

[denise456]

Welcome Fox

[NourAyman]

So Good .. Welcome Back Fox ^_^

[fuddreckers2]

welcome back ..fox
nice one

[Hex1337]

nice one <3

[tk69]

Msg 102, Level 15, State 1, Procedure _Any_2_Shop, Line 46
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Procedure _Any_2_Shop, Line 46
Incorrect syntax near '+'.
Msg 137, Level 15, State 2, Procedure _Any_2_Shop, Line 58
Must declare the scalar variable "@INDEX".
Msg 137, Level 15, State 2, Procedure _Any_2_Shop, Line 62
Must declare the scalar variable "@INDEX".
Msg 102, Level 15, State 1, Procedure _Any_2_Shop, Line 66
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Procedure _Any_2_Shop, Line 66
Incorrect syntax near '+'.
Msg 137, Level 15, State 1, Procedure _Any_2_Shop, Line 69
Must declare the scalar variable "@SLOT".
Msg 137, Level 15, State 2, Procedure _Any_2_Shop, Line 74
Must declare the scalar variable "@SLOT".
Msg 137, Level 15, State 2, Procedure _Any_2_Shop, Line 77
Must declare the scalar variable "@SLOT".


Help

- - - Updated - - -

Welcome Fox

• denise456 has exceeded their stored private messages quota and cannot accept further messages until they clear some space.

[Crue]

so i can add more payment methods ?

[arabianfox]

so i can add more payment methods ?

yes..you can add one or more payment methods at the same time

[Crue]

yes..you can add one or more payment methods at the same time

i mean add new payment like Gift silk

[Tazdingo]

Nice one arabian,

I change it to fit me neccesities.

btw you use too much variables when you can use only 1 to disable or enable the paymentdevice.

Since is a release i will post me edited procedure, its works only for Gold, Mall Points and Cooper Coin or whatever it called (id: 64).

exec [_Any_2_Shop2] 'ITEM_','STORE_TAB2',0,10000000,70,0

* First one is gold, second one is points and third one is cooper coin. If you want to disable just put "0" like i did at the end for disable cooper coins payment.

[longway]

Msg 102, Level 15, State 1, Procedure _Any_2_Shop, Line 46
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Procedure _Any_2_Shop, Line 46
Incorrect syntax near '+'.
Msg 137, Level 15, State 2, Procedure _Any_2_Shop, Line 58
Must declare the scalar variable "@INDEX".
Msg 137, Level 15, State 2, Procedure _Any_2_Shop, Line 62
Must declare the scalar variable "@INDEX".
Msg 102, Level 15, State 1, Procedure _Any_2_Shop, Line 66
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Procedure _Any_2_Shop, Line 66
Incorrect syntax near '+'.
Msg 137, Level 15, State 1, Procedure _Any_2_Shop, Line 69
Must declare the scalar variable "@SLOT".
Msg 137, Level 15, State 2, Procedure _Any_2_Shop, Line 74
Must declare the scalar variable "@SLOT".
Msg 137, Level 15, State 2, Procedure _Any_2_Shop, Line 77
Must declare the scalar variable "@SLOT".

[SunGm]

What should i do ?

Msg 208, Level 16, State 6, Procedure _Any_2_Shop, Line 160
Invalid object name 'dbo._Any_2_Shop'.

[F50]

use this procedure

USE [SRO_VT_SHARD]
GO
/****** Object:  StoredProcedure [dbo].[_Any_2_Shop]    Script Date: 1/14/2014 4:49:16 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[_Any_2_Shop]
    @ITEM varchar (64)  [MENTION=373843]shop[/MENTION]_TAB varchar (64),@ITEM_PLUS INT,
    @Payment_method_1 INT,@Payment_method_2 INT,@Payment_method_3 INT,@Payment_method_4 INT,@Payment_method_5 INT,@Payment_method_6 INT,
    @Payment_method_7 INT,@Payment_method_8 INT,
  [MENTION=828510]price[/MENTION]_1 INT  [MENTION=828510]price[/MENTION]_2 INT  [MENTION=828510]price[/MENTION]_3 INT  [MENTION=828510]price[/MENTION]_4 INT  [MENTION=828510]price[/MENTION]_5 INT  [MENTION=828510]price[/MENTION]_6 INT  [MENTION=828510]price[/MENTION]_7 INT  [MENTION=828510]price[/MENTION]_8 INT
    
AS    

--//ITEM CHECK//---
IF NOT EXISTS (SELECT CodeName128 FROM _RefObjCommon WHERE CodeName128 = @ITEM)
 BEGIN
  raiserror('This item %s is not exist!',11,1,@ITEM);
  RETURN;
 END

  --//TAB CHECK//---
 IF NOT EXISTS (SELECT CodeName128 FROM _RefShopTab WHERE CodeName128 =  [MENTION=373843]shop[/MENTION]_TAB)
 BEGIN
  raiserror('This TAB %s is not exist!',11,1  [MENTION=373843]shop[/MENTION]_TAB);
  RETURN;
 END

   --//PACKAGE ITEM NAME CHECK//---
 IF EXISTS (SELECT CodeName128 FROM _RefPackageItem WHERE CodeName128 = 'PACKAGE_'+@ITEM)
 BEGIN
  raiserror('This packageitem name %s is already exist!.Try to rename it',11,1,@ITEM);
  RETURN;
 END

  Declare  [MENTION=751524]pack[/MENTION]ID int SET  [MENTION=751524]pack[/MENTION]ID = (SELECT MAX (ID) FROM _RefPackageItem)+1
 Declare  [MENTION=1333447910]Icon[/MENTION] Varchar (MAX) SET  [MENTION=1333447910]Icon[/MENTION] = (select AssocFileIcon128 from _RefObjCommon where CodeName128 = @ITEM)
 Declare @SN varchar(MAX) SET @SN = (SELECT NameStrID128 FROM _RefObjCommon WHERE CodeName128 = @ITEM)
 Declare  [MENTION=275999]des[/MENTION]C_ITEM varchar(MAX) SET  [MENTION=275999]des[/MENTION]C_ITEM = (SELECT DescStrID128 FROM _RefObjCommon WHERE CodeName128 = @ITEM)
 
 --//Add Packageitem//---
 SET IDENTITY_INSERT _RefPackageItem ON
 INSERT INTO _RefPackageItem (Service,Country,ID,CodeName128,SaleTag,ExpandTerm,NameStrID,DescStrID,AssocFileIcon,Param1,Param1_Desc128,Param2,Param2_Desc128,Param3,Param3_Desc128,Param4,Param4_Desc128) VALUES
 (1,15  [MENTION=751524]pack[/MENTION]ID,'PACKAGE_'+@ITEM,0,'EXPAND_TERM_ALL',@SN  [MENTION=275999]des[/MENTION]C_ITEM  [MENTION=1333447910]Icon[/MENTION],-1,'xxx',-1,'xxx',-1,'xxx',-1,'xxx')
 SET IDENTITY_INSERT _RefPackageItem OFF
                print ''
                print 'Add the following line @ refpackageitem.txt'
                print '1    15    '+CONVERT(varchar(max)  [MENTION=751524]pack[/MENTION]ID)+'    PACKAGE_'+@ITEM+'    0    EXPAND_TERM_ALL    '+@SN+'    '  [MENTION=275999]des[/MENTION]C_ITEM+'    '  [MENTION=1333447910]Icon[/MENTION]+'    -1    xxx    -1    xxx    -1    xxx    -1    xxx'
                print ''
 
 DECLARE  [MENTION=1333459381]Index[/MENTION] INT = (SELECT MAX ([INDEX]) FROM _RefScrapOfPackageItem)+1
 DECLARE  [MENTION=1333359883]data[/MENTION] INT
 
 IF @ITEM LIKE '%RING%' OR @ITEM LIKE '%EARRING%' OR @ITEM LIKE '%NECKLACE%' OR @ITEM LIKE '%ARCHEMY_MAGICSTONE%' OR @ITEM LIKE '%ARCHEMY_ATTRSTONE%' BEGIN
    SET  [MENTION=1333359883]data[/MENTION] = 1
 END
 ELSE BEGIN
    SET  [MENTION=1333359883]data[/MENTION] = (SELECT Dur_L from _RefObjItem where ID = (select Link from _RefObjCommon where CodeName128 = @ITEM))
 END
 --//Add Scrap//---
  SET IDENTITY_INSERT _RefScrapOfPackageItem ON
 INSERT INTO _RefScrapOfPackageItem (Service,Country,RefPackageItemCodeName,RefItemCodeName,OptLevel,Variance,Data,MagParamNum,MagParam1,MagParam2,MagParam3,MagParam4,MagParam5,MagParam6,MagParam7,MagParam8,MagParam9,MagParam10,MagParam11,MagParam12,Param1,Param1_Desc128,Param2,Param2_Desc128,Param3,Param3_Desc128,Param4,Param4_Desc128,[Index]) VALUES
 (1,15,'PACKAGE_'+@ITEM,@ITEM,@ITEM_PLUS,0  [MENTION=1333359883]data[/MENTION],0,0,0,0,0,0,0,0,0,0,0,0,0,-1,'xxx',-1,'xxx',-1,'xxx',-1,'xxx'  [MENTION=1333459381]Index[/MENTION])
  SET IDENTITY_INSERT _RefScrapOfPackageItem OFF
                print ''
                print 'Add the following line @ refscrapofpackageitem.txt'
                print '1    15    '+'PACKAGE_'+@ITEM+'    '+@ITEM+'    '+CONVERT(varchar(max),@ITEM_PLUS)+'    0    '+CONVERT(varchar(max)  [MENTION=1333359883]data[/MENTION])+'    0    0    0    0    0    0    0    0    0    0    0    0    0    -1    xxx    -1    xxx    -1    xxx    -1    xxx    '+CONVERT(varchar(max)  [MENTION=1333459381]Index[/MENTION])
                print ''
 
 IF EXISTS (SELECT RefTabCodeName from _RefShopGoods where RefTabCodeName =  [MENTION=373843]shop[/MENTION]_TAB) BEGIN
 DECLARE  [MENTION=558012]slot[/MENTION] INT = (SELECT MAX (SlotIndex) FROM _RefShopGoods where RefTabCodeName =  [MENTION=373843]shop[/MENTION]_TAB)+1
 END
 ELSE BEGIN
 SET  [MENTION=558012]slot[/MENTION] = 0
 END
 
  --//Add SHOP//---
 INSERT INTO _RefShopGoods (Service,Country,RefTabCodeName,RefPackageItemCodeName,SlotIndex,Param1,Param1_Desc128,Param2,Param2_Desc128,Param3,Param3_Desc128,Param4,Param4_Desc128) VALUES
 (1,15  [MENTION=373843]shop[/MENTION]_TAB,'PACKAGE_'+@ITEM  [MENTION=558012]slot[/MENTION],-1,'xxx',-1,'xxx',-1,'xxx',-1,'xxx')
                print ''
                print 'Add the following line @ RefShopGoods.txt'
                print '1    15    '  [MENTION=373843]shop[/MENTION]_TAB+'    '+'PACKAGE_'+@ITEM+'    '+CONVERT(varchar(max)  [MENTION=558012]slot[/MENTION])+'    -1    xxx    -1    xxx    -1    xxx    -1    xxx'
                print ''
 
 --//Add Prices//---
 IF @Payment_method_1 = 1 BEGIN
 INSERT INTO _RefPricePolicyOfItem(Service,Country,RefPackageItemCodeName,PaymentDevice,PreviousCost,Cost,Param1,Param1_Desc128,Param2,Param2_Desc128,Param3,Param3_Desc128,Param4,Param4_Desc128) VALUES
 (1,15,'PACKAGE_'+@ITEM,1,0  [MENTION=828510]price[/MENTION]_1,-1,'xxx',-1,'xxx',-1,'xxx',-1,'xxx')
                print ''
                print 'Add the following line @ RefPricePolicyOfItem.txt'
                print '1    15    PACKAGE_'+@ITEM+'    1    0    '+CONVERT(varchar(max)  [MENTION=828510]price[/MENTION]_1)+'    -1    xxx    -1    xxx    -1    xxx    -1    xxx'
                print ''
 END
 
 IF @Payment_method_2 = 2 BEGIN
 INSERT INTO _RefPricePolicyOfItem(Service,Country,RefPackageItemCodeName,PaymentDevice,PreviousCost,Cost,Param1,Param1_Desc128,Param2,Param2_Desc128,Param3,Param3_Desc128,Param4,Param4_Desc128) VALUES
 (1,15,'PACKAGE_'+@ITEM,2,0  [MENTION=828510]price[/MENTION]_2,-1,'xxx',-1,'xxx',-1,'xxx',-1,'xxx')
 INSERT INTO _RefPricePolicyOfItem(Service,Country,RefPackageItemCodeName,PaymentDevice,PreviousCost,Cost,Param1,Param1_Desc128,Param2,Param2_Desc128,Param3,Param3_Desc128,Param4,Param4_Desc128) VALUES
 (1,15,'PACKAGE_'+@ITEM,4,0,0,-1,'xxx',-1,'xxx',-1,'xxx',-1,'xxx')
 INSERT INTO _RefPricePolicyOfItem(Service,Country,RefPackageItemCodeName,PaymentDevice,PreviousCost,Cost,Param1,Param1_Desc128,Param2,Param2_Desc128,Param3,Param3_Desc128,Param4,Param4_Desc128) VALUES
 (1,15,'PACKAGE_'+@ITEM,16,0,0,-1,'xxx',-1,'xxx',-1,'xxx',-1,'xxx')
                print ''
                print 'Add the following line @ RefPricePolicyOfItem.txt'
                print '1    15    PACKAGE_'+@ITEM+'    2    0    '+CONVERT(varchar(max)  [MENTION=828510]price[/MENTION]_2)+'    -1    xxx    -1    xxx    -1    xxx    -1    xxx'
                print '1    15    PACKAGE_'+@ITEM+'    4    0    0    -1    xxx    -1    xxx    -1    xxx    -1    xxx'
                print '1    15    PACKAGE_'+@ITEM+'    16    0    0    -1    xxx    -1    xxx    -1    xxx    -1    xxx'
                print ''
 END
 
 IF @Payment_method_3 = 32 BEGIN
 INSERT INTO _RefPricePolicyOfItem(Service,Country,RefPackageItemCodeName,PaymentDevice,PreviousCost,Cost,Param1,Param1_Desc128,Param2,Param2_Desc128,Param3,Param3_Desc128,Param4,Param4_Desc128) VALUES
 (1,15,'PACKAGE_'+@ITEM,32,0  [MENTION=828510]price[/MENTION]_3,-1,'xxx',-1,'xxx',-1,'xxx',-1,'xxx')
                print ''
                print 'Add the following line @ RefPricePolicyOfItem.txt'
                print '1    15    PACKAGE_'+@ITEM+'    32    0    '+CONVERT(varchar(max)  [MENTION=828510]price[/MENTION]_3)+'    -1    xxx    -1    xxx    -1    xxx    -1    xxx'
                print ''
 END
 
 IF @Payment_method_4 = 64 BEGIN
 INSERT INTO _RefPricePolicyOfItem(Service,Country,RefPackageItemCodeName,PaymentDevice,PreviousCost,Cost,Param1,Param1_Desc128,Param2,Param2_Desc128,Param3,Param3_Desc128,Param4,Param4_Desc128) VALUES
 (1,15,'PACKAGE_'+@ITEM,64,0  [MENTION=828510]price[/MENTION]_4,-1,'xxx',-1,'xxx',-1,'xxx',-1,'xxx')
                print ''
                print 'Add the following line @ RefPricePolicyOfItem.txt'
                print '1    15    PACKAGE_'+@ITEM+'    64    0    '+CONVERT(varchar(max)  [MENTION=828510]price[/MENTION]_4)+'    -1    xxx    -1    xxx    -1    xxx    -1    xxx'
                print ''
 END
 
 IF @Payment_method_5 = 128 BEGIN
 INSERT INTO _RefPricePolicyOfItem(Service,Country,RefPackageItemCodeName,PaymentDevice,PreviousCost,Cost,Param1,Param1_Desc128,Param2,Param2_Desc128,Param3,Param3_Desc128,Param4,Param4_Desc128) VALUES
 (1,15,'PACKAGE_'+@ITEM,128,0  [MENTION=828510]price[/MENTION]_5,-1,'xxx',-1,'xxx',-1,'xxx',-1,'xxx')
                print ''
                print 'Add the following line @ RefPricePolicyOfItem.txt'
                print '1    15    PACKAGE_'+@ITEM+'    128    0    '+CONVERT(varchar(max)  [MENTION=828510]price[/MENTION]_5)+'    -1    xxx    -1    xxx    -1    xxx    -1    xxx'
                print ''
 END
 
 IF @Payment_method_6 = 256 BEGIN
 INSERT INTO _RefPricePolicyOfItem(Service,Country,RefPackageItemCodeName,PaymentDevice,PreviousCost,Cost,Param1,Param1_Desc128,Param2,Param2_Desc128,Param3,Param3_Desc128,Param4,Param4_Desc128) VALUES
 (1,15,'PACKAGE_'+@ITEM,256,0  [MENTION=828510]price[/MENTION]_6,-1,'xxx',-1,'xxx',-1,'xxx',-1,'xxx')
                print ''
                print 'Add the following line @ RefPricePolicyOfItem.txt'
                print '1    15    PACKAGE_'+@ITEM+'    256    0    '+CONVERT(varchar(max)  [MENTION=828510]price[/MENTION]_6)+'    -1    xxx    -1    xxx    -1    xxx    -1    xxx'
                print ''
 END
 
 IF @Payment_method_7 = 512 BEGIN
 INSERT INTO _RefPricePolicyOfItem(Service,Country,RefPackageItemCodeName,PaymentDevice,PreviousCost,Cost,Param1,Param1_Desc128,Param2,Param2_Desc128,Param3,Param3_Desc128,Param4,Param4_Desc128) VALUES
 (1,15,'PACKAGE_'+@ITEM,512,0  [MENTION=828510]price[/MENTION]_7,-1,'xxx',-1,'xxx',-1,'xxx',-1,'xxx')
                print ''
                print 'Add the following line @ RefPricePolicyOfItem.txt'
                print '1    15    PACKAGE_'+@ITEM+'    512    0    '+CONVERT(varchar(max)  [MENTION=828510]price[/MENTION]_7)+'    -1    xxx    -1    xxx    -1    xxx    -1    xxx'
                print ''
 END
 
 IF @Payment_method_8 = 1024 BEGIN
 INSERT INTO _RefPricePolicyOfItem(Service,Country,RefPackageItemCodeName,PaymentDevice,PreviousCost,Cost,Param1,Param1_Desc128,Param2,Param2_Desc128,Param3,Param3_Desc128,Param4,Param4_Desc128) VALUES
 (1,15,'PACKAGE_'+@ITEM,1024,0  [MENTION=828510]price[/MENTION]_8,-1,'xxx',-1,'xxx',-1,'xxx',-1,'xxx')
                print ''
                print 'Add the following line @ RefPricePolicyOfItem.txt'
                print '1    15    PACKAGE_'+@ITEM+'    1024    0    '+CONVERT(varchar(max)  [MENTION=828510]price[/MENTION]_8)+'    -1    xxx    -1    xxx    -1    xxx    -1    xxx'
                print ''
 END
 
 IF @Payment_method_1 = 0 OR @Payment_method_2 = 0 OR @Payment_method_3 = 0 OR @Payment_method_4 = 0 OR @Payment_method_5 = 0 OR @Payment_method_6 = 0 OR @Payment_method_7 = 0 OR @Payment_method_8 = 0 BEGIN
 RETURN
 END
 
 --//Written by Arabianfox//--- 

replace ALTER by CREATE

[naldjer]

Very nice,but there is one payment method missing... And that should be number 8 Guild Points =)