熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> SQL Server >> 正文

SQL Server編寫存儲過程小工具

2013-11-15 14:35:12  來源: SQL Server 

  在開發數據庫系統的過程中經常要寫很多的存儲過程為了統一格式和簡化開發過程我編寫一些存儲過程用來自動生成存儲過程下面就為您簡單介紹一下它們其中一個用於生成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 EmployeesEmployeeIDUPD_Employees
  
  運行後生成如下所示的存儲過程
  
  Create Procedure UPD_Employees
  
  @EmployeeID int
  
  @LastName nvarchar()
  
  @FirstName nvarchar()
  
  @Title nvarchar()
  
  @TitleofCourtesy nvarchar()
  
  @BirthDate datetime
  
  @HireDate datetime
  
  @Address nvarchar()
  
  @City nvarchar()
  
  @Region nvarchar()
  
  @PostalCode nvarchar()
  
  @Country nvarchar()
  
  @HomePhone nvarchar()
  
  @Extension nvarchar()
  
  @Phote image
  
  @Notes ntext
  
  @ReportsTo int
  
  @PhotoPath nvarchar()
  
  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()
  @ProcedureName varchar()
  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 as colorder into #TempProc
  union
  select convert(char()@ + syscolumnsname)
  + rtrim(systypesname)
  + case when rtrim(systypesname) in (binarycharncharnvarcharvarbinaryvarchar) then ( + rtrim(convert(char()syscolumnslength)) + )
  when rtrim(systypesname) not in (binarycharncharnvarcharvarbinaryvarchar) 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 ,,
  以northwind 數據庫為例
  sp_GenUpdate 'Employees','EmployeeID','UPD_Employees'
  
  注釋:如果您在Master系統數據庫中創建該過程,那您就可以在您服務器上所有的數據庫中使用該過程。tW.WIngWIT.cOm
  
  ===========================================================*/
  CREATE procedure sp_GenUpdate
  @TableName varchar(130),
  @PrimaryKey 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 <> @PrimaryKey and systypes.name <> 'sysname'
  union
  select 'WHERE ' + @PrimaryKey + ' = @' + @PrimaryKey,(2 * @maxcol) + 4 as colorder
  order by colorder
  
  
  select type from #tempproc order by colorder
  
  drop table #tempproc
  /*===============源程序結束==================*/
  

From:http://tw.wingwit.com/Article/program/SQLServer/201311/22023.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.