WEB开发网
开发学院数据库MSSQL Server Stored Procedure(存储过程)编写经验和优化措施... 阅读

Stored Procedure(存储过程)编写经验和优化措施

 2007-11-11 08:01:05 来源:WEB开发网   
核心提示: 在开发数据库系统的过程中,经常要写很多的存储过程,Stored Procedure(存储过程)编写经验和优化措施(4),为了统一格式和简化开发过程,我编写一些存储过程,那您就可以在您服务器上所有的数据库中使用该过程,==*/CREATE procedure sp_GenInsert@TableName varchar

  在开发数据库系统的过程中,经常要写很多的存储过程。为了统一格式和简化开发过程,我编写一些存储过程,用来自动生成存储过程。下面就为您简单介绍一下它们。其中一个用于生成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 >,< Prima(最完善的虚拟主机管理系统)ry 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
  
  
  使用以上的两个存储过程,节省了我不少时间。特别是在改变了表结构后,重新构造各个存储过程的过程中。您可以改写这两个程序,来自动生成别的存储过程。
  以下是两个存储过程的源程序
  /*==================================================================
  
  语法: sp_GenInsert ,
  以northwind 数据库为例
  sp_GenInsert 'Employees', 'INS_Employees'
  
  注释:如果您在Master系统数据库中创建该过程,那您就可以在您服务器上所有的数据库中使用该过程。
  
  ==================================================================*/
  
  CREATE procedure sp_GenInsert
  @TableName varchar(130),
  @ProcedureName varchar(130)
  as
  set nocount on
  
  declare @maxcol int,
  @TableID int
  
  set @TableID = object_id(@TableName)
  
  select @MaxCol = max(colorder)
  from syscolumns
  where id = @TableID
  
  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 'INSERT INTO ' + @TableName,@maxcol + 2 as colorder
  union
  select '(',@maxcol + 3 as colorder
  union
  select 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 systypes.name <> 'sysname'
  union
  select ')',(2 * @maxcol) + 4 as colorder
  union
  select 'VALUES',(2 * @maxcol) + 5 as colorder
  union
  select '(',(2 * @maxcol) + 6 as colorder
  union
  select '@' + syscolumns.name
  + case when colorder < @maxcol then ','
  when colorder = @maxcol then ' '
  end
  as type,
  colorder + (2 * @maxcol + 6) as colorder
  from syscolumns
  join systypes on syscolumns.xtype = systypes.xtype
  where id = @TableID and systypes.name <> 'sysname'
  union
  select ')',(3 * @maxcol) + 7 as colorder
  order by colorder
  
  
  select type from #tempproc order by colorder
  
  drop table #tempproc
  功能:为给定表创建Update存储过程
  语法: sp_GenUpdate
,(最完善的虚拟主机管理系统)ry Key>,
  以northwind 数据库为例
  sp_GenUpdate 'Employees','EmployeeID','UPD_Employees'
  
  注释:如果您在Master系统数据库中创建该过程,那您就可以在您服务器上所有的数据库中使用该过程。
  
  ===========================================================*/
  CREATE procedure sp_GenUpdate
  @TableName varchar(130),
  @Prima(最完善的虚拟主机管理系统)ryKey varchar(130),
  @ProcedureName varchar(130)
  as
  set nocount on
  
  declare @maxcol int,
  @TableID int
  
  set @TableID = object_id(@TableName)
  
  select @MaxCol = max(colorder)
  from syscolumns
  where id = @TableID
  
  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 <> @Prima(最完善的虚拟主机管理系统)ryKey and systypes.name <> 'sysname'
  union
  select 'WHERE ' + @Prima(最完善的虚拟主机管理系统)ryKey + ' = @' + @Prima(最完善的虚拟主机管理系统)ryKey,(2 * @maxcol) + 4 as colorder
  order by colorder
  
  
  select type from #tempproc order by colorder
  
  drop table #tempproc
  /*===============源程序结束==================*/

上一页  1 2 3 4 5 6 7 8 9  下一页

Tags:Stored Procedure 存储

编辑录入:coldstar [复制链接] [打 印]
赞助商链接