[SQL] SP _Addsilk + LOG-Table

For the hell of it - could be eventually useful for Event-Rewards. And if several ones got DB access to let others know why and when certain ppl gained some extra Silk :)


http://s14.directupload.net/images/120306/fbggmyyg.jpg


Create Procedure (Adds automatically the LOG-Table if it doesn't exist in SRO_VT_ACCOUNT:

PHP Code:

---

USE [SRO_VT_ACCOUNT]
GO
/****** Object:  StoredProcedure [dbo].[_Silkadd]    Script Date: 03/06/2012 12:44:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[_Silkadd]

    @Charname varchar(max),
    @Silkamount int,
    @Comment varchar(max)
    
as

SET NOCOUNT ON

IF not exists(
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME like '_Silkadd_LOG'
)
BEGIN
CREATE TABLE [dbo].[_Silkadd_LOG](
    [CharID] [int] NULL,
    [CharName16] [varchar](max) NULL,
    [Silkamount] [int] NULL,
    [executionDate] [smalldatetime] NULL,
    [Comment] [varchar](max) NULL DEFAULT NULL,
    [JID] [int] NULL,
    [CurTotalSilk] [int] NULL
)
END

SET NOCOUNT OFF

IF not exists    (
SELECT CharName16 FROM SRO_VT_SHARD.dbo._Char WHERE CharName16 = @Charname
                )
BEGIN
return -1
ROLLBACK TRANSACTION
raiserror ('Charname does not exist!',16,1)
END

SET NOCOUNT ON

IF  not exists (
SELECT sksilk.JID
FROM /*SRO_VT_ACCOUNT.dbo.*/SK_Silk as sksilk
JOIN /*SRO_VT_ACCOUNT.dbo.*/TB_User as tbuser on sksilk.JID = tbuser.JID
JOIN SRO_VT_SHARD.dbo._AccountJID as accjid on tbuser.JID = accjid.JID
JOIN SRO_VT_SHARD.dbo._User as usert on accjid.JID = usert.UserJID
JOIN SRO_VT_SHARD.dbo._Char as chart on usert.CharID = chart.CharID
WHERE chart.CharName16 = @Charname
)
BEGIN
INSERT INTO SK_Silk (JID, silk_own, silk_gift, silk_point) 
VALUES    (
(
SELECT tbuser.JID
FROM TB_User as tbuser
JOIN SRO_VT_SHARD.dbo._AccountJID as accjid on tbuser.JID = accjid.JID
JOIN SRO_VT_SHARD.dbo._User as usert on accjid.JID = usert.UserJID
JOIN SRO_VT_SHARD.dbo._Char as chart on usert.CharID = chart.CharID
WHERE chart.CharName16 = @Charname
),
0 + @Silkamount,
0,
0
        )
END

ELSE    BEGIN
UPDATE SK_Silk SET silk_own = silk_own + @Silkamount WHERE JID =
    (
    SELECT sksilk.JID
    FROM /*SRO_VT_ACCOUNT.dbo.*/SK_Silk as sksilk
    JOIN /*SRO_VT_ACCOUNT.dbo.*/TB_User as tbuser on sksilk.JID = tbuser.JID
    JOIN SRO_VT_SHARD.dbo._AccountJID as accjid on tbuser.JID = accjid.JID
    JOIN SRO_VT_SHARD.dbo._User as usert on accjid.JID = usert.UserJID
    JOIN SRO_VT_SHARD.dbo._Char as chart on usert.CharID = chart.CharID
    WHERE chart.CharName16 = @Charname
    )
        END
        
INSERT INTO _Silkadd_LOG
(CharID, CharName16, Silkamount, executionDate, Comment, JID, CurTotalSilk)
VALUES
        (
(
SELECT chart.CharID
FROM /*SRO_VT_ACCOUNT.dbo.*/SK_Silk as sksilk
JOIN /*SRO_VT_ACCOUNT.dbo.*/TB_User as tbuser on sksilk.JID = tbuser.JID
JOIN SRO_VT_SHARD.dbo._AccountJID as accjid on tbuser.JID = accjid.JID
JOIN SRO_VT_SHARD.dbo._User as usert on accjid.JID = usert.UserJID
JOIN SRO_VT_SHARD.dbo._Char as chart on usert.CharID = chart.CharID
WHERE chart.CharName16 = @Charname
),
(
SELECT chart.CharName16
FROM /*SRO_VT_ACCOUNT.dbo.*/SK_Silk as sksilk
JOIN /*SRO_VT_ACCOUNT.dbo.*/TB_User as tbuser on sksilk.JID = tbuser.JID
JOIN SRO_VT_SHARD.dbo._AccountJID as accjid on tbuser.JID = accjid.JID
JOIN SRO_VT_SHARD.dbo._User as usert on accjid.JID = usert.UserJID
JOIN SRO_VT_SHARD.dbo._Char as chart on usert.CharID = chart.CharID
WHERE chart.CharName16 = @Charname
),
(@Silkamount),
(GETDATE()),
(@Comment),
(
SELECT sksilk.JID
FROM /*SRO_VT_ACCOUNT.dbo.*/SK_Silk as sksilk
JOIN /*SRO_VT_ACCOUNT.dbo.*/TB_User as tbuser on sksilk.JID = tbuser.JID
JOIN SRO_VT_SHARD.dbo._AccountJID as accjid on tbuser.JID = accjid.JID
JOIN SRO_VT_SHARD.dbo._User as usert on accjid.JID = usert.UserJID
JOIN SRO_VT_SHARD.dbo._Char as chart on usert.CharID = chart.CharID
WHERE chart.CharName16 = @Charname
),
(
SELECT sksilk.silk_own
FROM /*SRO_VT_ACCOUNT.dbo.*/SK_Silk as sksilk
JOIN /*SRO_VT_ACCOUNT.dbo.*/TB_User as tbuser on sksilk.JID = tbuser.JID
JOIN SRO_VT_SHARD.dbo._AccountJID as accjid on tbuser.JID = accjid.JID
JOIN SRO_VT_SHARD.dbo._User as usert on accjid.JID = usert.UserJID
JOIN SRO_VT_SHARD.dbo._Char as chart on usert.CharID = chart.CharID
WHERE chart.CharName16 = @Charname
)
        )
        
SET NOCOUNT OFF 


---

Call Procedure in a New Query - Window:

PHP Code:

---

exec SRO_VT_ACCOUNT.dbo._Silkadd 'Charname',Silkamount,'Comment' 


---

e.g.

PHP Code:

---

exec SRO_VT_ACCOUNT.dbo._Silkadd 'Caipi',150,'' 


---

leave Comment '' if you dun want to add one


PS: if you type the Charname incorrectly it neither adds it to SK_Silk nor to the LOG table

EDIT: and YES the SP can be simplified but since the performance doesn't matter that much right now (I doubt that it'll be increased that extraordinarily) I'll leave it like that now

Nice Bro...keep it up ^_^ #1