通过表明生成存储过程

/*
调用示例:
cp t_goods

生成常用存储过程
*/

ALTER        procedure cp
@tablename varchar(50) = '' -- 表名称
    
as    

declare @tableid int     
set @tableid = object_id(@tablename)

if @tableid is null 
begin    
    select 'table not exists' as error
    return    
end

declare @tmptb table(code varchar(4000))

declare @fieldsName varchar(4000)
declare @parmName varchar(4000)
declare @keyname varchar(50)
declare @proctablename varchar(50)

declare @ident_seed int
select @ident_seed = ident_seed(@tablename)
if(@ident_seed is null)
    set @ident_seed = -1

set @proctablename = @tablename 
if( left(@proctablename,2) ='t_')
    set @proctablename = substring(@proctablename,3,len(@proctablename) - 2)

print @proctablename

select top 1 @keyname= [name] from syscolumns where id = @tableid order by colid 

set @fieldsname = ''
select @fieldsname = @fieldsname + a.[name] + ',' 
from syscolumns a inner join systypes b on a.xtype = b.xtype
where id = @tableid and a.colid <> @ident_seed
and b.name <> 'datetime' and b.name <> 'smalldatetime'
order by colid

if( right(@fieldsname,1) = ',' )
    set @fieldsname = substring(@fieldsname,1,len(@fieldsname) - 1)

set @parmname = ''
select @parmname = @parmname + '@' + a.[name] + ',' 
from syscolumns a inner join systypes b on a.xtype = b.xtype
where id = @tableid and a.colid <> @ident_seed
and b.name <> 'datetime' and b.name <> 'smalldatetime'
order by colid

if( right(@parmname,1) = ',' )
    set @parmname = substring(@parmname,1,len(@parmname) - 1)

-- proc_pagelist1
delete from @tmptb

insert into @tmptb select 'create procedure p_' + @proctablename + '_list'

insert into @tmptb 
select '@page int = 1, --当前页' union all
select '@pagesize int = 10, --每页显示' union all
select '@total int = 0 output, --总记录数' union all
select '@typeid int = 0 ''这里需要修改''' union all
select 'as' union all
select 'set nocount on' union all
select 'set transaction isolation level read uncommitted' union all
select 'set xact_abort on' union all
select ''

insert into @tmptb 
select 'if( @total = 0 )' union all
select '    select @total = count(1) from ' + @tablename + ' where typeid = @typeid ''这里需要修改''' union all
select '' union all
select 'if( @page = 1 )' union all
select 'begin' union all
select '    set rowcount @pagesize' union all
select '    select * from ' + @tablename + ' where typeid = @typeid ''这里需要修改'' order by ' + @keyname +  ' desc ' union all
select '    set rowcount 0' union all
select '    return' union all
select 'end' union all
select 'else' union all
select 'begin' union all
select '    declare @tb table(rownum int identity(1,1),keyid int)' union all
select '    insert into @tb(keyid) select ' + @keyname + ' from ' + @tablename union all
select '        where typeid = @typeid ''这里需要修改'' order by ' + @keyname +  ' desc' union all
select '    select a.* from ' + @tablename + ' a inner join @tb b on a.'+ @keyname +  ' = b.keyid ' union all
select '        where b.rownum > @pagesize * (@page - 1) and b.rownum <= @pagesize * @page' union all
select 'end'

insert into @tmptb select '' union all select 'go'

select code as proc_pagelist1 from @tmptb

-- proc_pagelist2
delete from @tmptb

insert into @tmptb select 'create procedure p_' + @proctablename + '_list'

insert into @tmptb 
select '@page int = 1, --当前页' union all
select '@pagesize int = 10, --每页显示' union all
select '@total int = 0 output, --总记录数' union all
select '@typeid int = 0 ''这里需要修改''' union all
select 'as' union all
select 'set nocount on' union all
select 'set transaction isolation level read uncommitted' union all
select 'set xact_abort on' union all
select ''

insert into @tmptb 
select 'declare @tablename varchar(1024)' union all           
select 'declare @orderby  varchar(1024)' union all      
select 'declare @orderby2 varchar(1024)' union all      
select 'declare @orderbyclause varchar(1024)' union all      
select 'declare @keyname varchar(50)' union all      
select 'declare @keyinorder tinyint' union all
select 'declare @whereclause varchar(1024)' union all      
select 'declare @showclause varchar(1024)' union all    
select '' union all        
select '-- 表名称' union all
select 'set @tablename = ''' + @tablename + ''' ' union all           
select '--排序字段,字段前面加b.,字段后加 desc或asc,格式如 b.rid desc,b.rname asc' union all
select 'set @orderby = ''b.' + @keyname + ' desc'' ' union all           
select '--排序字段,字段前面加b.,字段后面和上面的orderby正好相反,格式如 b.rid asc,b.ranme desc' union all
select 'set @orderby2 = ''b.' + @keyname + ' asc'' '  union all
select '--排序字段，这里不需要加 asc 和 desc，格式如 b.rid,b.rname' union all
select 'set @orderbyclause = ''b.' + @keyname + '''' union all
select '--主键是否在排序字段里，1 是 0 不是，一般是1' union all
select 'set @keyinorder = 1'  union all           
select '--这个表的主键' union all
select 'set @keyname = ''' + @keyname +  ''''  union all           
select '--查询条件' union all
select 'set @whereclause = '' where typeid = '' + convert(varchar,@typeid) ' + '''这里需要修改'''  union all           
select 'set @showclause = ''a.*'''   union all             
select '' union all
select '--调用分页存储过程' union all
select 'exec P_SplitPageOneSql_v2 @tablename,@pagesize,@page,@orderby,@orderby2,' union all           
select '@orderbyclause,@keyname,@keyinorder,@whereclause,@showclause,@total output'

insert into @tmptb select '' union all select 'go'

select code as proc_pagelist2 from @tmptb



-- proc_insert
delete from @tmptb

insert into @tmptb select 'create procedure p_' + @proctablename + '_insert'

insert into @tmptb 
select 
'@' + a.name + ' ' + 
case b.name 
when 'char' then 'char(' + convert(varchar,a.length) + ') = '''','    
when 'nchar' then 'nchar(' + convert(varchar,a.length) + ')= '''','    
when 'varchar' then 'varchar(' + convert(varchar,a.length) + ') = '''','    
when 'nvarchar' then 'nvarchar(' + convert(varchar,a.length) + ') = '''','
when 'datetime' then 'datetime,'
when 'smalldatetime' then 'smalldatetime,'
else b.name + ' = 0,' end
from syscolumns a inner join systypes b    
on a.xtype = b.xtype     
where a.id = object_id(@tablename) 
and a.colid <> @ident_seed
and b.name <> 'datetime' and b.name <> 'smalldatetime'
order by colid 

insert into @tmptb 
select 'as' union all
select 'set nocount on' union all
select 'set transaction isolation level read uncommitted' union all
select 'set xact_abort on' union all
select ''

insert into @tmptb 
select 'insert into ' + @tablename + '(' + @fieldsname + ')' union all
select '    values(' + @parmname + ')'

insert into @tmptb select '' union all select 'go'

select code as proc_insert from @tmptb

-- proc_update
delete from @tmptb

insert into @tmptb select 'create procedure p_' + @proctablename + '_update'

insert into @tmptb 
select 
'@' + a.name + ' ' + 
case b.name 
when 'char' then 'char(' + convert(varchar,a.length) + ') = '''','    
when 'nchar' then 'nchar(' + convert(varchar,a.length) + ')= '''','    
when 'varchar' then 'varchar(' + convert(varchar,a.length) + ') = '''','    
when 'nvarchar' then 'nvarchar(' + convert(varchar,a.length) + ') = '''','
when 'datetime' then 'datetime,'
when 'smalldatetime' then 'smalldatetime,'
else b.name + ' = 0,' end
from syscolumns a inner join systypes b    
on a.xtype = b.xtype     
where a.id = object_id(@tablename)    
and b.name <> 'datetime' and b.name <> 'smalldatetime'
order by colid 

insert into @tmptb 
select 'as' union all
select 'set nocount on' union all
select 'set transaction isolation level read uncommitted' union all
select 'set xact_abort on' union all
select ''

set @fieldsname = ''
select @fieldsname = @fieldsname + a.[name] + '=' + '@' + a.[name] + ',' 
from syscolumns a inner join systypes b on a.xtype = b.xtype
where id = @tableid and a.colid <> @ident_seed
and b.name <> 'datetime' and b.name <> 'smalldatetime'
order by colid


if( right(@fieldsname,1) = ',' )
    set @fieldsname = substring(@fieldsname,1,len(@fieldsname) - 1)


insert into @tmptb 
select 'update ' + @tablename union all
select ' set ' + @fieldsname  union all
select ' where ' + @keyname + '=@' + @keyname

insert into @tmptb select '' union all select 'go'

select code as proc_update from @tmptb


-- proc_delete
delete from @tmptb

insert into @tmptb select 'create procedure p_' + @proctablename + '_delete'

insert into @tmptb 
select top 1
'@' + a.name + ' ' + 
case b.name 
when 'char' then 'char(' + convert(varchar,a.length) + ') = '''''    
when 'nchar' then 'nchar(' + convert(varchar,a.length) + ')= '''''    
when 'varchar' then 'varchar(' + convert(varchar,a.length) + ') = '''''    
when 'nvarchar' then 'nvarchar(' + convert(varchar,a.length) + ') = '''''
else b.name + ' = 0' end
from syscolumns a inner join systypes b    
on a.xtype = b.xtype     
where a.id = object_id(@tablename)    
order by a.colid 

insert into @tmptb 
select 'as' union all
select 'set nocount on' union all
select 'set transaction isolation level read uncommitted' union all
select 'set xact_abort on' union all
select ''

insert into @tmptb select 'delete from ' + @tablename + ' where ' + @keyname + '=@' + @keyname

insert into @tmptb select '' union all select 'go'

select code as proc_delete from @tmptb


-- proc_detail
delete from @tmptb

insert into @tmptb select 'create procedure p_' + @proctablename + '_detail'

insert into @tmptb 
select top 1
'@' + a.name + ' ' + 
case b.name 
when 'char' then 'char(' + convert(varchar,a.length) + ') = '''''    
when 'nchar' then 'nchar(' + convert(varchar,a.length) + ')= '''''    
when 'varchar' then 'varchar(' + convert(varchar,a.length) + ') = '''''    
when 'nvarchar' then 'nvarchar(' + convert(varchar,a.length) + ') = '''''
else b.name + ' = 0' end
from syscolumns a inner join systypes b    
on a.xtype = b.xtype     
where a.id = object_id(@tablename)    
order by a.colid 

insert into @tmptb 
select 'as' union all
select 'set nocount on' union all
select 'set transaction isolation level read uncommitted' union all
select 'set xact_abort on' union all
select ''

insert into @tmptb select 'select * from ' + @tablename + ' where ' + @keyname + '=@' + @keyname

insert into @tmptb select '' union all select 'go'

select code as proc_detail from @tmptb

-- proc_listall
delete from @tmptb
insert into @tmptb select 'create procedure p_' + @proctablename + '_listall'

insert into @tmptb 
select 'as' union all
select 'set nocount on' union all
select 'set transaction isolation level read uncommitted' union all
select 'set xact_abort on' union all
select ''

insert into @tmptb 
select 'select * from ' + @tablename 

insert into @tmptb select '' union all select 'go'

select code as proc_listall from @tmptb


go