动态为表添加存储时间字段
实现动态化,为表添加存储时间字段,Insus.NET写一个存储过程,如下
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Insus.NET -- Blog: https://insus.cnblogs.com -- Create date: 2019-05-29 -- Update date: 2019-05-29 -- Description: 动态为表添加存储时间字段 -- ============================================= CREATE PROCEDURE [dbo].[usp_DyanmicallyAddStorageDatetimeField] ( @TABLE_CATALOG SYSNAME, @TABLE_SCHEMA SYSNAME, @TABLE_NAME SYSNAME, @COLUMNs NVARCHAR(MAX) -- '[column1],[column2],[column3]...' ) AS BEGIN DECLARE @source TABLE ([ID] INT IDENTITY(1,1), [value] NVARCHAR(MAX)) INSERT INTO @source ([value]) SELECT [value] FROM [dbo].[tvf_ConvertStringToTable](@COLUMNs,',') DECLARE @r INT = 1,@rs INT = 0 SELECT @rs = MAX([ID]) FROM @source WHILE @r <= @rs BEGIN DECLARE @COLUMN_NAME SYSNAME SELECT @COLUMN_NAME = CONVERT(VARCHAR(30), [value], 23) FROM @source WHERE [ID] = @r EXECUTE('IF [dbo].[usp_IsExistsColumn]('''+ @TABLE_CATALOG +''','''+ @TABLE_SCHEMA +''','''+ @TABLE_NAME +''','''+ @COLUMN_NAME +''') = 0 ALTER TABLE '+ @TABLE_NAME +' ADD '+ @COLUMN_NAME +' DATETIME') SET @r= @r + 1 END END
上面存储过程代码#54行中有一个定义函数:[dbo].[tvf_ConvertStringToTable]()
是分割字符串转为表。函数详细代码参考这篇《展开中断或忽略的序号》https://www.cnblogs.com/insus/p/10929956.html
还有一个自定义函数:[dbo].[usp_IsExistsColumn]()这是判断表的列是否存在。
参考:《判断列名是否存在》https://www.cnblogs.com/insus/p/10943356.html
接下来,Insus.NET演示这个存储过程[dbo].[usp_DyanmicallyAddStorageDatetimeField]:
IF OBJECT_ID('tempdb..#TempRpt') IS NOT NULL DROP TABLE #TempRpt CREATE TABLE #TempRpt ( [caseno] bigint, [username] nvarchar(20) ) EXECUTE [dbo].[usp_DyanmicallyAddStorageDatetimeField] 'tempdb','dbo','#TempRpt','[column1],[column2],[column3]' SELECT * FROM #TempRpt