MSSQL Server编写存款和储蓄进度小工具(一State of Qatar

在开发数据库系统的过程中,为了统一格式和简化开发过程,为了统一格式和简化开发过程,GenInsert,GenUpdate Table Name,SQL Server编写存储过程小工具 功能

@EmployeeID int

eNet能力大学,版权全体
在支付数据库系统的经过中,日常要写过多的存款和储蓄进程。为了统一格式和简化开拓进度,作者编写一些存款和储蓄进程,用来自动生成存款和储蓄进度。上边就为你简介一下它们。个中四个用来生成Insert进程,另一个用以生成Update进度。
 
Sp_GenInsert
该进程运维后,它为给定的表生成多个完完全全的Insert进程。假使原来的表有标记列,您得将调换的长河中的SET
IDNTITY_INSERT ON 语句手工业删除。
 
语法如下
sp_GenInsert < Table Name >,< Stored Procedure Name >
以northwind 数据库为例
sp_GenInsert ’Employees’, ’INS_Employees’
终极会调换八个Insert存储进度。利用它,您能够作进一层的付出。
 
Sp_GenUpdate
它会为贰个表生成update存款和储蓄进程。语法如下:
sp_GenUpdate < Table Name >,< Primary Key >,< Stored
Procedure Name >
以northwind 数据库为例
sp_GenUpdate ’Employees’,’EmployeeID’,’UPD_Employees’
运营后生成如下所示的积攒进程:
Create Procedure UPD_Employees
@EmployeeID int
@LastName nvarchar(40) ,
@FirstName nvarchar(20) ,
@Title nvarchar(60) ,
@TitleofCourtesy nvarchar(50) ,
@BirthDate datetime ,
@HireDate datetime ,
@Address nvarchar(120) ,
@City nvarchar(30) ,
@Region nvarchar(30) ,
@PostalCode nvarchar(20) ,
@Country nvarchar(30) ,
@HomePhone nvarchar(48) ,
@Extension nvarchar(8) ,
@Phote image ,
@Notes ntext ,
@ReportsTo int ,
@PhotoPath nvarchar(510)
AS
UPDATE Employees
SET
LastName = @LastName,
FirstName = @FirstName,
Title = @Title,
TitleofCourtesy = @TitleofCourtesy,
BirthDate = @BirthDate,
HireDate = @HireDate,
Address = @Address,
City = @City,
Regin = @Regin,
PostalCode = @PostCode,
Country = @Country,
HomePhone = @HomePhone,
Extension = @Extension,
Photo = @Photo
Notes = @Notes,
ReportsTo = @ReportsTo,
PhotoPath = @PhotoPath
WHERE EmployeeID = @EmployeeID
选用上述的七个存款和储蓄进度,节省了自己好多日子。特别是在改造了表布局后,重新布局各类仓库储存进程的长河中。您能够改写那八个程序,来自动生成其余累积进度。

select type from #tempproc order by colorder

终极会扭转多个Insert存款和储蓄进度。利用它,您能够作进一层的开支。

select Create Procedure + rtrim(@ProcedureName) as type,0 as colorder
into #TempProc union select convert(char(35),@ + syscolumns.name) +
rtrim(systypes.name) + case when rtrim(systypes.name) in
(binary,char,nchar,nvarchar,varbinary,varchar) then ( +
rtrim(convert(char(4),syscolumns.length)) + ) when rtrim(systypes.name)
not in (binary,char,nchar,nvarchar,varbinary,varchar) then end + case
when colorder @maxcol then , when colorder = @maxcol then end as type,
colorder from syscolumns join systypes on syscolumns.xtype =
systypes.xtype where id = @TableID and systypes.name sysname union
select AS,@maxcol + 1 as colorder union select UPDATE +
@TableName,@maxcol + 2 as colorder union select SET,@maxcol + 3 as
colorder union select syscolumns.name + = @ + syscolumns.name + case
when colorder @maxcol then , when colorder = @maxcol then end as type,
colorder + @maxcol + 3 as colorder from syscolumns join systypes on
syscolumns.xtype = systypes.xtype where id = @TableID and
syscolumns.name @PrimaryKey and systypes.name sysname union select WHERE

在支付数据库系统的进程中,平常要写过多的存款和储蓄进程。为了统一格式和简化开荒进度,笔者编写一些积攒进度,用来自动生成存款和储蓄进程。上面就为您简要介绍一下它们。个中叁个用以生成Insert进度,另叁个用于生成Update进程。

注解:假如你在Master系统数据库中开创该进度,那您就足以在您服务器上有着的数据库中动用该进程。

Create Procedure UPD_Employees

drop table #tempproc /*=======源程序停止=========*/

SET

select @MaxCol = max(colorder) from syscolumns where id = @TableID

sp_GenUpdate Table Name , Primary Key , Stored Procedure Name

核心提醒:SQL
Server编写存款和储蓄进程小工具成效:为给定表创造Update存款和储蓄进程

运作后生成如下所示的蕴藏进度:

  • @PrimaryKey + = @ + @PrimaryKey,(2 * @maxcol) + 4 as colorder order
    by colorder

Photo = @Photo

declare @maxcol int, @TableID int knowsky.com set @TableID =
object_id(@TableName)

AS

SQL Server编写存款和储蓄进程小工具 效能:为给定表创制Update存款和储蓄进度 语法:
sp_GenUpdate Table Name,Primary Key,Stored Procedure Name 以northwind
数据库为例 sp_GenUpdate Employees,EmployeeID,UPD_Employees

ReportsTo = @ReportsTo,

===========================================================*/ CREATE
procedure sp_GenUpdate @TableName varchar(130), @PrimaryKey
varchar(130), @ProcedureName varchar(130) as set nocount on

@ReportsTo int ,

Extension = @Extension,

PhotoPath = @PhotoPath

HireDate = @HireDate,

它会为三个表生成update存款和储蓄进程。语法如下:

接受上述的五个存款和储蓄进度,节省了自身多数时间。特别是在改造了表布局后,重新布局种种仓库储存进程的进程中。您能够改写那四个程序,来自动生成别的囤积进程。

PostalCode = @PostCode,

sp_GenInsert ’Employees’, ’INS_Employees’

sp_GenInsert Table Name , Stored Procedure Name