betway必威-betway必威官方网站
做最好的网站

Server编写存储过程小工具,依赖函数

核心提示:SQL Server编写存储过程小工具功能:为给定表创建Update存储过程

USE [XXX] GO

SQL Server编写存储过程小工具 功能:为给定表创建Update存储过程 语法: sp_GenUpdate Table Name,Primary Key,Stored Procedure Name 以northwind 数据库为例 sp_GenUpdate Employees,EmployeeID,UPD_Employees

QUOTED_IDENTIFIER ON GO

注释:如果您在Master系统数据库中创建该过程,那您就可以在您服务器上所有的数据库中使用该过程。

ALTER PROCEDURE [dbo].[UspOutputData]

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

@tablename sysname

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

AS

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

declare @column varchar(2000)

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

declare @columndata varchar(2000)

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

declare @sql varchar(4000)

select type from #tempproc order by colorder

declare @xtype tinyint

drop table #tempproc /*betway必威官方网站,=======源程序结束=========*/

declare @name sysname

declare @objectId int

declare @objectname sysname

declare @ident int

set nocount on set @objectId=object_id(@tablename)

if @objectId is null -- 判斷對象是否存在

begin

print 'The object not exists'

return

end

set @objectname=rtrim(object_name(@objectId))

if @objectname is null or charindex(@objectname,@tablename)=0 --此判断不严密

begin

print 'object not in current database'

return

end

if OBJECTPROPERTY(@objectId,'IsTable') < > 1 -- 判斷對象是否是table

本文由betway必威发布于编程开发,转载请注明出处:Server编写存储过程小工具,依赖函数

TAG标签: betway必威
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。