靜態給表添加刪除字段並同時修正它的拔出更新存儲進程

靜態給表添加刪除字段並同時修正它的拔出更新存儲進程。本站提示廣大學習愛好者：（靜態給表添加刪除字段並同時修正它的拔出更新存儲進程）文章只能為提供參考，不一定能成為您想要的結果。以下是靜態給表添加刪除字段並同時修正它的拔出更新存儲進程正文

---

有一個表，用戶須要在後台操作它，願望能對它靜態停止添加刪除字段。這個功效或許沒有成績，然則它原有拔出與更新的兩個存儲進程，也須要一路修正。是以Insus.NET完成了它，是以此文會讓你懂得到如何靜態為一個表添加刪除字段和靜態修正它的存儲進程

起首須要建一個表[A]，這個表只要兩個字段，一個是[ID]主動增加,另外一個是表[B]的字段名，存儲的每筆記載，等於用戶須要操作的表[B]的字段。這個表[A]須要建添加，更新，和刪除的存儲進程，便利用戶在後台便利操作，還有重點部門，須要寫觸發器。若有記載對表[A]停止添加，更新或是刪除時，它會觸發去作表[B]響應操作，還要去修正表[B]的存儲進程。

靜態修正表[B]的存儲進程：

CREATE PROCEDURE [dbo].[usp_B_DymanicallyAlterStoreProcedure]
AS
DECLARE @VariableList NVARCHAR(MAX) = ''
DECLARE @FieldList NVARCHAR(MAX) = ''
DECLARE @ValueList NVARCHAR(MAX) = ''
DECLARE @FieldValueList NVARCHAR(MAX) = ''

DECLARE @I INT = 1, @R INT = 0
SET @R = (SELECT MAX([Id]) FROM [dbo].[A])
WHILE (@I <= @R)
BEGIN
DECLARE @fName NVARCHAR(100)
IF EXISTS(SELECT [Id] FROM [dbo].[A] WHERE [Id] = @I)
BEGIN
SELECT @fName = [FieldName] FROM [dbo].[A] WHERE [Id] = @I
SET @VariableList = @VariableList + ',@' + @fName +' DECIMAL(18,4)' --靜態的字段數據類型都一樣
SET @FieldList = @FieldList + ',[' + @fName + ']'
SET @ValueList = @ValueList + ',@' + @fName
SET @FieldValueList = @FieldValueList + ',[' + @fName + '] = @' + @fName
END
SET @I = @I + 1
END

DECLARE @sql_I NVARCHAR(MAX),@sql_U NVARCHAR(MAX)
SET @sql_I = '
ALTER PROCEDURE [dbo].[usp_B_Insert]
(
@ItemCode NVARCHAR(50)
'+ @VariableList +'
)
AS
INSERT INTO [dbo].[B] ([ItemCode]'+ @FieldList +') VALUES (@ItemCode'+ @ValueList +')
'
EXECUTE sp_EXECUTESQL @sql_I;

SET @sql_U = '
ALTER PROCEDURE [dbo].[usp_B_Update]
(
@Id INT,
@ItemCode NVARCHAR(50)
'+ @VariableList +'
)
AS
UPDATE [dbo].[B] SET [ItemCode] = @ItemCode'+ @FieldValueList +' WHERE [Id] = @Id
'
EXECUTE sp_EXECUTESQL @sql_U;

表[A]的拔出觸發器：

CREATE TRIGGER [dbo].[tri_A_Insert] ON [dbo].[A]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON
DECLARE @FieldName NVARCHAR(50)
SELECT @FieldName = [FieldName] FROM INSERTED

EXECUTE('IF NOT EXISTS(SELECT * FROM SYSCOLUMNS WHERE [id] = OBJECT_ID(''B'') AND [name] = '''+ @FieldName +''')
ALTER TABLE [B] ADD ['+ @FieldName +'] DECIMAL(18,4) NULL')

EXECUTE [dbo].[usp_B_DymanicallyAlterStoreProcedure];
END

表[A]刪除觸發器：

CREATE TRIGGER [dbo].[tri_A_Delete] ON [dbo].[A]
FOR DELETE
AS
BEGIN
SET NOCOUNT ON
DECLARE @FieldName NVARCHAR(50)
SELECT @FieldName = [FieldName] FROM DELETED

EXECUTE('IF EXISTS(SELECT * FROM SYSCOLUMNS WHERE [id] = OBJECT_ID(''B'') AND [name] = '''+ @FieldName +''')
ALTER TABLE [B] DROP COLUMN ['+ @FieldName +']')

EXECUTE [dbo].[usp_B_DymanicallyAlterStoreProcedure];
END