fPrPS7j5

1ALTER PROC [dbo].[manage_table] @serverID VARCHAR(100), @servername VARCHAR(1000)
2AS 
3
4DECLARE @idtest VARCHAR(100), @count INT;
5DECLARE @nametest VARCHAR(100), @oldname VARCHAR(100), @newname VARCHAR(100);
6SET @servername = 'tbl' + @servername;
7SELECT @idtest = (SELECT ServerID FROM tblServerID WITH (NOLOCK) WHERE ServerID = @serverID)
8SELECT @nametest = (SELECT ServerName FROM tblServerID WITH (NOLOCK) WHERE ServerID = @serverID)
9
10
11/*Handler to create table if no entry exists for serverid and name. If servername is null, its safe to create table based off servername. */
12IF (OBJECT_ID(@servername) IS NULL) AND (@idtest IS NULL) AND (@nametest IS NULL)
13BEGIN
14EXECUTE ('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)');
15INSERT INTO tblServerID (ServerID, ServerName) 
16VALUES (@serverid, @servername);
17END
18
19/*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*/
20ELSE IF OBJECT_ID(@servername) IS NOT NULL AND @idtest IS NULL
21BEGIN
22SET @count = 2
23SET @newname = @servername
24		WHILE OBJECT_ID(@newname) IS NOT NULL
25	    BEGIN
26		    SET @newname = @servername
27		    SET @newname = @newname + (CONVERT(VARCHAR,@count))
28			SET @count = @count + 1
29		END
30EXECUTE ('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)');
31INSERT INTO tblServerID (ServerID, ServerName)
32VALUES (@serverid, @newname);
33END
34
35/*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.*/
36ELSE IF(OBJECT_ID(@servername) IS NULL) AND @idtest IS NOT NULL
37BEGIN
38	SELECT @oldname = (SELECT ServerName FROM tblServerID WITH (NOLOCK) WHERE ServerID = @serverID)
39	SET @newname = @servername
40	EXEC sp_rename @oldname, @newname
41	UPDATE tblServerID 
42	SET ServerName = @newname
43	WHERE ServerID = @serverID;
44END
45
46/*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.*/
47ELSE IF OBJECT_ID(@servername) IS NOT NULL AND @idtest IS NOT NULL AND @nametest NOT LIKE '%' + @servername + '%'
48BEGIN
49	SET @count = 2
50	SET @newname = @servername
51	SET @oldname = @nametest
52
53		WHILE OBJECT_ID(@newname) IS NOT NULL
54	    BEGIN
55		    SET @newname = @servername
56		    SET @newname = @newname + (CONVERT(VARCHAR,@count))
57			SET @count = @count + 1
58		END
59
60	EXEC sp_rename @oldname, @newname
61	UPDATE tblServerID 
62	SET ServerName = @newname
63	WHERE ServerID = @serverID;
64END