一个自动生成预制SQL 的存储过程（一）

前言

　　我们工作的时候经常会有一些数据是在开发期整理好，到客户那预制到处据库中去，于是乎就有了下面的存储过程。

=======SQL server 版本====================================================
/*exec HRspGenInsertSQL 'HRobjects','HRobjects_NM','2'
@tablename 表名
@tableNM  表内码字段名
@ifExists 是否包含if exists 判断语句 1:包含;0:不包含;2:如果存在先删除，再插入*/
IF EXISTS (SELECT name  FROM   sysobjects  WHERE  NAME = 'HRspGenInsertSQL'   AND  type = 'P')
    DROP PROCEDURE HRspGenInsertSQL
GO 
Create PROCEDURE HRspGenInsertSQL
(@tablename varchar(400),@tableNM varchar(400),@ifExists char(1))
as
declare @sql varchar(8000) 
declare @sqlValues varchar(8000) 
declare @sqlInsert varchar(8000)
begin 
CREATE TABLE #UsertableData (
 Row int IDENTITY(1,1) PRIMARY KEY,
 datasql varchar(8000)
)
                          
Declare @NM varchar(36)
execute('Declare cur Cursor for select '+@tableNM+' from '+ @tablename+' order by '+@tableNM + '')
open cur
fetch next from cur into @NM
while (@@fetch_status=0)
begin
  
set @sql =' (' 
set @sqlValues = 'values (''+' 
select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql  + name + ',' 
  from 
      (select case 
                when xtype in (48,52,56,59,60,62,104,106,108,122,127)       
                     then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end' 
                when xtype in (58,61) 
                     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end' 
               when xtype in (167) 
                     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end' 
                when xtype in (231) 
                     then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end' 
                when xtype in (175) 
                     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end' 
                when xtype in (239) 
                     then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end' 
                else '''NULL''' 
              end as Cols,name 
         from syscolumns 
        where id = object_id(@tablename) 
      ) T 
 
if(LTRIM(@ifExists) != '' and LTRIM(@ifExists) = '1')
begin
 set @sqlInsert = ' if not exists ( select 1 from '+@tablename+' where '+@tablename+'.'+@tableNM+' = '''+@NM+''') ';
 set @sqlInsert = REPLACE(@sqlInsert,'''','''''');
 exec('insert into #UsertableData(datasql) values(''' + @sqlInsert +''')')
end
else if(LTRIM(@ifExists) = '2')
begin
 set @sqlInsert = ' if  exists ( select 1 from '+@tablename+' where '+@tablename+'.'+@tableNM+' = '''+@NM+''') ';
 set @sqlInsert = REPLACE(@sqlInsert,'''','''''');
 exec('insert into #UsertableData(datasql) values(''' + @sqlInsert +''')')
 
 set @sqlInsert = ' delete from '+@tablename+' where '+@tablename+'.'+@tableNM+' = '''+@NM+''' ';
 set @sqlInsert = REPLACE(@sqlInsert,'''','''''');
 exec('insert into #UsertableData(datasql) values(''' + @sqlInsert +''')')
 insert into #UsertableData(datasql)
 values ('go');
end

set @sqlInsert = 'select '+'''INSERT INTO '+@tablename+''+ left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')''
from ' + @tablename +' where '+@tablename+'.'+@tableNM+' = '''+@NM+''' ';
EXEC('insert into #UsertableData(datasql)' + @sqlInsert )
insert into #UsertableData(datasql)
values ('go');
fetch next from cur into @NM
end
close cur;
Deallocate cur
select datasql from #UsertableData order by  Row
drop table #UsertableData
END
go

转载于:https://www.cnblogs.com/songmh/archive/2011/03/27/1997072.html