内容简介:实现动态化,为表添加存储时间字段,Insus.NET写一个存储过程,如下上面存储过程代码#54行中有一个定义函数:[dbo].[tvf_ConvertStringToTable]()
实现动态化,为表添加存储时间字段,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
Source Code
上面存储过程代码#54行中有一个定义函数:[dbo].[tvf_ConvertStringToTable]()
是分割字符串转为表。函数详细代码参考这篇《展开中断或忽略的序号》 https://www.cnblogs.com/insus/p/10929956.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
Source Code
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:- springboot~DTO字符字段与日期字段的转换问题
- Protocol Buffers 学习(2):字段类型和其他语言字段类型之间的映射
- Protocol Buffers 学习(2):字段类型和其他语言字段类型之间的映射
- PHPRAP 2.0.2 发布,接口和字段数据分离,字段使用单独数据表
- 如何正确新增字段
- HTTP缓存字段总结
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
The Smashing Book
Jacob Gube、Dmitry Fadeev、Chris Spooner、Darius A Monsef IV、Alessandro Cattaneo、Steven Snell、David Leggett、Andrew Maier、Kayla Knight、Yves Peters、René Schmidt、Smashing Magazine editorial team、Vitaly Friedman、Sven Lennartz / 2009 / $ 29.90 / € 23.90
The Smashing Book is a printed book about best practices in modern Web design. The book shares technical tips and best practices on coding, usability and optimization and explores how to create succes......一起来看看 《The Smashing Book》 这本书的介绍吧!