[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
Re: [SQL] SP _Addsilk + LOG-Table
Nice Bro...keep it up ^_^ #1