9LpKLrcN

1USE [db_discord]
2GO
3/****** Object:  StoredProcedure [dbo].[manage_table]    Script Date: 10/29/2018 10:31:00 AM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8ALTER PROC [dbo].[manage_table] @serverID VARCHAR(100), @servername VARCHAR(1000)
9AS 
10
11DECLARE @idtest VARCHAR(100), @count INT;
12DECLARE @nametest VARCHAR(100), @oldname VARCHAR(100), @newname VARCHAR(100);
13SET @servername = 'tbl' + @servername;
14SELECT @idtest = (SELECT ServerID FROM tblServerID WITH (NOLOCK) WHERE ServerID = @serverID)
15SELECT @nametest = (SELECT ServerName FROM tblServerID WITH (NOLOCK) WHERE ServerID = @serverID)
16
17
18/*Handler to create table if no entry exists for serverid and name. If servername is null, its safe to create table based off servername. */
19IF (OBJECT_ID(@servername) IS NULL) AND (@idtest IS NULL) AND (@nametest IS NULL)
20BEGIN
21EXECUTE ('USE db_discord CREATE TABLE ' + @servername + '( EntryID INT IDENTITY (1,1), UserChannel VARCHAR(100), UserName VARCHAR(100), UserID VARCHAR(20), UserMessage VARCHAR(1000), MessageTime DATETIME)');
22INSERT INTO tblServerID (ServerID, ServerName) 
23VALUES (@serverid, @servername);
24END
25
26/*Handler to create table and assign it a unique name if there is no ID entry in the routing table, but there is already a table named after servername*/
27ELSE IF OBJECT_ID(@servername) IS NOT NULL AND @idtest IS NULL
28BEGIN
29SET @count = 2
30SET @newname = @servername
31		WHILE OBJECT_ID(@newname) IS NOT NULL
32	    BEGIN
33		    SET @newname = @servername
34		    SET @newname = @newname + (CONVERT(VARCHAR,@count))
35			SET @count = @count + 1
36		END
37EXECUTE ('USE db_discord CREATE TABLE ' + @newname + '( EntryID INT IDENTITY (1,1), UserChannel VARCHAR(100), UserName VARCHAR(100), UserID VARCHAR(20), UserMessage VARCHAR(1000), MessageTime DATETIME)');
38INSERT INTO tblServerID (ServerID, ServerName)
39VALUES (@serverid, @newname);
40END
41
42/*Handler to rename a server if it already has an entry ID but servername has changed. Also checks to make sure the new name is not already in use.*/
43ELSE IF(OBJECT_ID(@servername) IS NULL) AND @idtest IS NOT NULL
44BEGIN
45	SELECT @oldname = (SELECT ServerName FROM tblServerID WITH (NOLOCK) WHERE ServerID = @serverID)
46	SET @newname = @servername
47	EXEC sp_rename @oldname, @newname
48	UPDATE tblServerID 
49	SET ServerName = @newname
50	WHERE ServerID = @serverID;
51END
52
53/*If all of the above checks fail, give the server a new unique name and update the routing table. This shouldn't exist, but i'm paranoid.*/
54ELSE IF OBJECT_ID(@servername) IS NOT NULL AND @idtest IS NOT NULL AND @nametest NOT LIKE '%' + @servername + '%'
55BEGIN
56	SET @count = 2
57	SET @newname = @servername
58	SET @oldname = @nametest
59
60		WHILE OBJECT_ID(@newname) IS NOT NULL
61	    BEGIN
62		    SET @newname = @servername
63		    SET @newname = @newname + (CONVERT(VARCHAR,@count))
64			SET @count = @count + 1
65		END
66
67	EXEC sp_rename @oldname, @newname
68	UPDATE tblServerID 
69	SET ServerName = @newname
70	WHERE ServerID = @serverID;
71END
72
73