7,765
社区成员
发帖
与我相关
我的任务
分享
-- =============================================
-- Author: shipeng.wang
-- Create date: 2010-12-31
-- Description: 根据表名,自动生成增、删、改参数化存储过程和调用代码
-- =============================================
create proc [dbo].[sp_SendCode]
@tablename varchar(20), --表名
@type int=1, --类型(1:存储过程,2:调用代码)
@opertype int=1, --操作类型(1:查,2:增,3:改,4:删)
@fields varchar(2000)='*',--要操作的列(查、增、改时有效,默认操作所有列,多个列用英文逗号隔开)
@where varchar(500)='' --要作为条件的列(查、改、删时有效,默认为空,多个列名用英文逗号隔开,如:字段1,字段2,其中主键列可以省略)
as
--不返回受影响的行数,提高性能
set nocount on
--分别定义主键列,主键列是否自增列,主键列的类型,最后要生成的代码
declare @keyfield varchar(20),@iden int,@partype varchar(20),@code varchar(4000)
select @keyfield=c.name,@iden=c.is_identity,@partype=d.name from sys.indexes a,sys.index_columns b,sys.columns c,systypes d
where a.object_id=b.object_id and a.index_id=b.index_id and a.object_id=c.object_id and c.user_type_id=d.xtype
and b.column_id=c.column_id and a.is_primary_key=1 and d.status=0
and a.object_id=OBJECT_ID(@tablename)
--查询时,是否只返回实体对象(1:是,0:不是)
declare @isflag bit
set @isflag=0
if(@where=@keyfield)
set @isflag=1
--进行非空处理
if(not exists(select 1 from sysobjects where id=OBJECT_ID(@tablename)))
begin
print '请输入正确的表名!'
return
end
--修改的列集合
declare @updatefields varchar(1000)
set @updatefields=''
--如果指定了要操作的列,进行校验。防止出现错误列
if(@fields!='' and @fields!='*')
begin
declare @oldfields varchar(200)
set @fields=REPLACE(@fields,',',',')
if(right(@fields,1)!=',')
set @fields=@fields+','
set @oldfields=@fields
set @fields=''
--遍历,筛选出@fields中的有效列
while(CHARINDEX(',',@oldfields)>0)
begin
--获取到字段名
declare @tempf varchar(50)
set @tempf=SUBSTRING(@oldfields,1,charindex(',',@oldfields)-1)
if exists(select 1 from syscolumns where id=OBJECT_ID(@tablename) and name=@tempf)
begin
set @fields=@fields+@tempf+','
set @updatefields=@updatefields+@tempf+'=@'+@tempf+','
end
set @oldfields=SUBSTRING(@oldfields,charindex(',',@oldfields)+1,LEN(@oldfields))
end
end
--如果没有有效字段,或是操作所有字段
if(@fields='*' or @fields='')
begin
set @fields=''
select @fields=@fields+name+',',@updatefields=@updatefields+case when name!=@keyfield then name+'=@'+name+',' else '' end
from syscolumns where id=OBJECT_ID(@tablename)
end
if(@updatefields!='')
set @updatefields=STUFF(@updatefields,LEN(@updatefields),1,'')
--保存条件
declare @tempwhere varchar(200)
set @tempwhere=''
--如果有条件时,也对条件进行相同处理
if(@where!='')
begin
declare @oldwhere varchar(200),@tempfield varchar(50)
set @where=REPLACE(@where,',',',')
if(right(@where,1)!=',')
set @where=@where+','
set @oldwhere=@where
set @where=''
--遍历,筛选出@fields中的有效列
while(CHARINDEX(',',@oldwhere)>0)
begin
set @tempfield=SUBSTRING(@oldwhere,1,charindex(',',@oldwhere)-1)
if exists(select 1 from syscolumns where id=OBJECT_ID(@tablename) and name=@tempfield)
begin
set @tempwhere=@tempwhere+@tempfield+','
set @where=@where+' and '+@tempfield+'=@'+@tempfield
end
set @oldwhere=SUBSTRING(@oldwhere,charindex(',',@oldwhere)+1,LEN(@oldwhere))
end
end
if(@where='' and (@opertype=3 or @opertype=4))
begin
--如果没有有效条件,则将主键作为条件
set @tempwhere=@keyfield+','
set @where=' and '+@keyfield+'=@'+@keyfield
end
--生成存储过程
if(@type=1)
begin
--增加时,如果操作列中 存在主键列,并且主键列为自增列时,排除该列
if(@opertype=2 and CHARINDEX(@keyfield,@fields)>0 and @iden=1)
begin
set @fields=stuff(@fields,charindex(@keyfield+',',@fields),LEN(@keyfield+','),'')
end
set @fields=stuff(@fields,LEN(@fields),1,'')
--生成存储过程的参数声明
declare @paras varchar(1000)
--生成存储过程
if(@opertype=1) --查询
begin
--根据条件来生成
select @paras=ISNULL(@paras+',' + CHAR(13),'')+'@'+a.name+' '+b.name
+case when b.name in('varchar','nvarchar','char','nchar') then '('+LTRIM(a.length)+')' else '' end
from syscolumns a,systypes b
where a.id=OBJECT_ID(@tablename) and a.xtype=b.xtype and CHARINDEX(','+a.name+',',','+@tempwhere+',')>0
and b.status=0
set @code ='/*-- ============================================='+CHAR(13)+
'-- Author: shipeng.wang'+CHAR(13)+
'-- Create date: '+CONVERT(varchar(10),getdate(),120)+CHAR(13)+
'-- Description: 对表'+@tablename+'中数据进行查询'+CHAR(13)+
'-- =============================================*/'+CHAR(13)
set @code=@code+'create proc [sp_'+@tablename+'_select'+case @isflag when 0 then '' else '_one' end +']'+CHAR(13)+isnull(@paras+ CHAR(13),'')
+'as'+CHAR(13)
+CHAR(9)+'select '+@fields+' from ['+@tablename +'] where 1=1'+ @where
+CHAR(13)
end
else if(@opertype=2)--增加
begin
--根据添加的字段来生成
select @paras=ISNULL(@paras+',' + CHAR(13),'')+'@'+a.name+' '+b.name
+case when b.name in('varchar','nvarchar','char','nchar') then '('+LTRIM(a.length)+')' else '' end
from syscolumns a,systypes b
where a.id=OBJECT_ID(@tablename) and a.xtype=b.xtype and CHARINDEX(','+a.name+',',','+@fields+',')>0
and b.status=0
set @code ='/*-- ============================================='+CHAR(13)+
'-- Author: shipeng.wang'+CHAR(13)+
'-- Create date: '+CONVERT(varchar(10),getdate(),120)+CHAR(13)+
'-- Description: 对表'+@tablename+'中数据进行添加'+CHAR(13)+
'-- =============================================*/'+CHAR(13)
set @code=@code+'create proc [sp_'+@tablename+'_insert]'+CHAR(13)+isnull(@paras+ CHAR(13),'')
+'as'+CHAR(13)
+CHAR(9)+'insert into ['+@tablename +'] ('+@fields+') values(@'+REPLACE(@fields,',',',@')+')'
+CHAR(13)
end
else if(@opertype=3) -- 修改
begin
--如果没有有效条件,则将主键作为条件
if(@where='')
begin
set @tempwhere=@keyfield
set @where=' and '+@keyfield+'=@'+@keyfield
end
--根据添加的字段和条件来生成
select @paras=ISNULL(@paras+',' + CHAR(13),'')+'@'+a.name+' '+b.name
+case when b.name in('varchar','nvarchar','char','nchar') then '('+LTRIM(a.length)+')' else '' end
from syscolumns a,systypes b
where a.id=OBJECT_ID(@tablename) and a.xtype=b.xtype and (CHARINDEX(','+a.name+',',','+@fields+',')>0 or CHARINDEX(','+a.name+',',','+@tempwhere+',')>0)
and b.status=0
set @code ='/*-- ============================================='+CHAR(13)+
'-- Author: shipeng.wang'+CHAR(13)+
'-- Create date: '+CONVERT(varchar(10),getdate(),120)+CHAR(13)+
'-- Description: 对表'+@tablename+'中数据进行修改'+CHAR(13)+
'-- =============================================*/'+CHAR(13)
set @code=@code+'create proc [sp_'+@tablename+'_update]'+CHAR(13)+isnull(@paras+ CHAR(13),'')
+'as'+CHAR(13)
+CHAR(9)+'update ['+@tablename +'] set '+@updatefields+' where 1=1'+@where
+CHAR(13)
end
else if(@opertype=4)
begin
--根据条件来生成
select @paras=ISNULL(@paras+',' + CHAR(13),'')+'@'+a.name+' '+b.name
+case when b.name in('varchar','nvarchar','char','nchar') then '('+LTRIM(a.length)+')' else '' end
from syscolumns a,systypes b
where a.id=OBJECT_ID(@tablename) and a.xtype=b.xtype and CHARINDEX(','+a.name+',',','+@tempwhere+',')>0
and b.status=0
set @code ='/*-- ============================================='+CHAR(13)+
'-- Author: shipeng.wang'+CHAR(13)+
'-- Create date: '+CONVERT(varchar(10),getdate(),120)+CHAR(13)+
'-- Description: 对表'+@tablename+'中数据进行删除'+CHAR(13)+
'-- =============================================*/'+CHAR(13)
set @code=@code+'create proc [sp_'+@tablename+'_delete]'+CHAR(13)+isnull(@paras+ CHAR(13),'')
+'as'+CHAR(13)
+CHAR(9)+'delete ['+@tablename +'] where 1=1'+@where
+CHAR(13)
end
end