澳门二十一点游戏赌场 澳门二十一点游戏赌场
    您现在的位置:首页 >> 软件应用 >> SQL教程 >> 内容

    自动生成对表进行插入和更新的存储过程的存储过程

    时间:2012/7/23 20:07:42 点击:

      核心提示:我找到了两个存储过程,能自动生成对一个数据表的插入和更新的存储过程,现在奉献给大家!插入:Create procedure sp_GenInsert@TableName varchar(130),@P...

    我找到了两个存储过程,能自动生成对一个数据表的插入和更新的存储过程,现在奉献给大家!

    插入:

    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

    更新:

    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

     

    作者:佚名 来源:不详

    共有评论 0相关评论
    发表我的评论
    • 大名:
    • 内容:
  • 澳门二十一点游戏赌场(www.0769online.com) © 2018 版权所有 All Rights Reserved. 邮箱:103150@qq.com 业务QQ:103150 手机:13549753683

    Power By Laoy8 SQL 3.0SP1 粤ICP备10098668号
  •