根据表名自动生成INSERT,UPDATE,DELETE,SELECT的SQL语句

TBPROC

CREATE Procedure TbProc
@model varchar(2),@pagename varchar(32),@object varchar(32),@autoField varchar(32)=null
as
set nocount on
select @model=upper(@model)
select @pagename=lower(@pagename)
select @object=upper(@object)
declare @head varchar(100),@headfct varchar(105),@para varchar(1500),@content varchar(5000)
declare @paravar varchar(2000) ,@saveStr varchar(3000) ,@deleteStr varchar(500) ,@selectFieldStr varchar(1000) ,@returnPk varchar(250),@pkeyStr varchar(500),@pkeyParaStr varchar(500)
select @head=@#@#,@headfct=@#@#,@para=@#@#,@content=@#@#
exec TbField @object,@autofield,@paravar output ,@saveStr output ,@deleteStr output ,@selectFieldStr output,@pkeyStr output,@pkeyParaStr output
select @head=@#create procedure p@#+@model+@pagename+@#_@#
--Head
print @#注意：单位换算和货币换算都是在存储过程里实现！@#
--Sav
select @headfct=@head+@#Sav@#
select @para=@paravar
select @content=@saveStr
select @headfct+char(13)+@para+char(13)+@#as@#+char(13)+@content+char(10)+char(13)+@#GO@#+char(13)
--Tree
select @para=@#@#
select @headfct=@head+@#Tree@#
select @content=@selectFieldStr
select @headfct+char(13)+@para+char(13)+@#as@#+char(13)+@content+char(10)+char(13)+@#GO@#+char(13)
--Del
select @headfct=@head+@#Del@#
select @para=@pkeyParaStr
--test
--select @paravar,charindex(@#@IsValid@#,@paravar)
if charindex(@#IsValid@#,@selectFieldStr)>0
select @deleteStr=replace(replace(@deleteStr,@#delete@#,@#update@#),@#where@#,@#set IsValid=0 where@#)
select @content=@deleteStr
select @headfct+char(13)+@para+char(13)+@#as@#+char(13)+@content+char(10)+char(13)+@#GO@#+char(13)
--Back
select @headfct=@head+@#Back@#
select @content=@selectFieldStr
select @headfct+char(13)+@para+char(13)+@#as@#+char(13)+@content+char(10)+char(13)+@#GO@#+char(13)


GO

TbField

CREATE procedure TbField
@object varchar(32),@autofield varchar(32),@string varchar(2000) output,@saveStr varchar(3000) output,@deleteStr varchar(500) output,@selectFieldStr varchar(1000) output,@pkeyStr varchar(500) output,@pkeyParaStr varchar(500) output
as
set nocount on
select @object=upper(@object)
declare @nameStr varchar(1000),@varStr varchar(1000),@updStr varchar(1500),@pkeyvarStr varchar(500)--,@pkeyParaStr varchar(150),@pkeyStr varchar(250) 作为了输出参数
select @string=@#@#,@nameStr=@#@#,@varStr=@#@#,@updStr=@#@#,@pkeyvarStr=@#@#,@pkeyStr=@#@#,@pkeyParaStr=@#@#
declare @moneyStr varchar(500)
select @moneyStr=@#@#
declare @i smallint
select @i=1
declare @fieldtb table(pk int identity,field varchar(32))
declare @attrtb table(field varchar(32),typename varchar(32),length smallint)
declare @field varchar(32)
declare curfield cursor for
select name from syscolumns where id=object_id(@OBJECT)
open curfield
fetch next from curfield into @field
while @@fetch_status=0
begin
insert @fieldtb(field) values(@field)
select @nameStr=@nameStr+@# @#+@field +@#,@#
select @varStr=@varStr+@#@@#+@field +@#,@#
select @updStr=@updStr+@field+@#=@@#+@field+@#,@#
if len(@updStr)>@i*100
begin
select @updStr=@updStr+char(13)+char(9)+char(9)
select @i=@i+1
end
fetch next from curfield into @field
end
close curfield
deallocate curfield
insert @attrtb
select distinct c.name,replace(replace(d.type_name,@#identity@#,@#@#),@#()@#,@#@#),c.length
from syscolumns c
inner join master.dbo.spt_datatype_info d on c.xtype = d.ss_dtype
where c.id=object_id(@OBJECT)
-----select * from @attrtb --测试
select @i=1
declare @typename varchar(32),@length varchar(5)
declare record cursor for
select a.*
from @fieldtb f inner join @attrtb a on f.field=a.field
order by f.pk
open record
fetch next from record into @field,@typename,@length
while @@fetch_status=0
begin
if @typename not in (@#varchar@#,@#nvarchar@#,@#char@#,@#nchar@#,@#text@#,@#ntext@#)
begin
select @length=case @typename when @#smalldatetime@# then 10
when @#datetime@# then 32
when @#bit@# then 1
else 16
end
if @typename in (@#money@#,@#smallmoney@#)
select @moneyStr=@moneyStr+@#@@#+@field+@#__@#+@typename+@#,@#
select @typename=@#varchar@#
end
select @string=@string+@# @@#+@field+@# @#+@typename+@#(@#+@length+@#)@#+@#,@#
if len(@string)>@i*128
begin
select @string=@string+char(13)
select @i=@i+1
end
fetch next from record into @field,@typename,@length
end
close record
deallocate record
--About pkeys
declare @pkeytb table(field varchar(32))
insert @pkeytb
select c.name
from syscolumns c inner join sysindexes i on c.id=i.id
where c.id=object_id(@object) and (i.status & 0x800)=0x800
and (c.name = index_col (@object, i.indid, 1) or
c.name = index_col (@object, i.indid, 2) or
c.name = index_col (@object, i.indid, 3) or
c.name = index_col (@object, i.indid, 4) or
c.name = index_col (@object, i.indid, 5) or
c.name = index_col (@object, i.indid, 6) or
c.name = index_col (@object, i.indid, 7) or
c.name = index_col (@object, i.indid, 8) or
c.name = index_col (@object, i.indid, 9) or
c.name = index_col (@object, i.indid, 10) or
c.name = index_col (@object, i.indid, 11) or
c.name = index_col (@object, i.indid, 12) or
c.name = index_col (@object, i.indid, 13) or
c.name = index_col (@object, i.indid, 14) or
c.name = index_col (@object, i.indid, 15) or
c.name = index_col (@object, i.indid, 16)
)
if (select count(*) from @pkeytb)>1
begin
declare curpkeys cursor for
select field from @pkeytb
open curpkeys
fetch next from curpkeys into @field
while @@fetch_status=0
begin
select @pkeyvarStr=@pkeyvarStr+@field+@#=@@#+@field+@# and @#
select @pkeyStr=@pkeyStr+@field+@#,@#
select @i=charindex(@#@@#+@field,@string)
if @i>0 select @pkeyParaStr=@pkeyParaStr+substring(@string,@i,charindex(@#,@#,@string,@i)-@i)+@#,@#
fetch next from curpkeys into @field
end
close curpkeys
deallocate curpkeys
select @pkeyvarStr=left(@pkeyvarStr,len(@pkeyvarStr)-3)
select @pkeyStr=left(@pkeyStr,len(@pkeyStr)-1)
select @pkeyParaStr=left(@pkeyParaStr,len(@pkeyParaStr)-1)
end
else if (select count(*) from @pkeytb)=1
begin
select @field=field from @pkeytb
select @pkeyvarStr=@field+@#=@@#+@field
select @pkeyStr=@field
select @i=charindex(@#@@#+@field,@string)
if @i>0 select @pkeyParaStr=substring(@string,@i,charindex(@#,@#,@string,@i)-@i)
end
if right(@string,1)=char(13)
select @string=left(@string,len(@string)-2)
else
select @string=left(@string,len(@string)-1)
select @nameStr=left(@nameStr,len(@nameStr)-1)
select @varStr=left(@varStr,len(@varStr)-1)
select @varStr=replace(@varStr,@#@ModDate@#,@#getdate()@#)
select @varStr=replace(@varStr,@#@IsValid@#,space(7)+@#1@#)
if right(@updStr,1)=char(9)
select @updStr=left(@updStr,len(@updStr)-4)
else
select @updStr=left(@updStr,len(@updStr)-1)
select @updStr=replace(@updStr,@#@ModDate@#,@#getdate()@#)
select @updStr=replace(@updStr,@#@IsValid@#,@#IsValid@#)
----处理@moneyStr
declare @covNameStr varchar(1000)
select @covNameStr=@nameStr
if @moneyStr<>@#@#
BEGIN
declare @itemStr varchar(50),@itemfield varchar(32),@itemtype varchar(20),@covStr varchar(50)
select @moneyStr=@#,@#+@moneyStr
select @moneyStr
select @i=1,@itemStr=substring(@moneyStr,@i+1,charindex(@#,@#,@moneyStr,@i+1)-@i-1)
while @itemStr<>@#@#
begin
select @itemfield=left(@itemStr,charindex(@#__@#,@itemstr)-1)
select @itemtype=right(@itemStr,len(@itemStr)-charindex(@#__@#,@itemStr)-1)
select @covStr=@#cast(@#+@itemfield+@# as @#+@itemtype+@#)@#
select @varStr=replace(@varStr,@itemfield,@covStr)
select @updStr=replace(@updStr,@itemfield,@covStr)
select @covNameStr=replace(@covNameStr,right(@itemfield,len(@itemfield)-1),space(len(@covStr)-len(@itemfield))+right(@itemfield,len(@itemfield)-1))
select @i=charindex(@#,@#,@moneyStr,@i+1)
if @i=len(@moneyStr)
break
else select @itemStr=substring(@moneyStr,@i+1,charindex(@#,@#,@moneyStr,@i+1)-@i-1)
end
END
--------------------
declare @insertStr varchar(2000),@updateStr varchar(2000),@selectStr varchar(500)--,@deleteStr varchar(500) 作为了输出参数
declare @returnPk varchar(250)--,@selectFieldStr varchar(1000) 作为了输出参数
if @autofield is null
select @insertStr=char(9)+@#insert @#+@object+@#(@#+@covNameStr+@#)@#+char(13)+char(9)+ @#values @#+space(len(@object))+@#(@#+@varStr+@#)@#
else
select @insertStr=space(3)+@#begin@#+char(13)+char(9)+@#declare @count int@#+char(13)+char(9)+@#select @count=count(*) from @#+@object+@# where substring(@#+@autofield+@#,3,4)=convert(varchar(4),getdate(),12)@#+char(13)+char(9)+@#select @@#+@autofield+@#=@#@#XX@#@#+convert(varchar(4),getdate(),12)+cast(@count+1 as varchar(16))@#+char(13)+char(13)+char(9)+@#insert @#+@object+@#(@#+@covNameStr+@#)@#+char(13)+char(9)+ @#values @#+space(len(@object))+@#(@#+@varStr+@#)@#+char(13)+space(3)+@#end@#
select @updateStr=char(9)+@#update @#+@object+char(13)+char(9)+@#set @#+@updStr+char(13)+char(9)+@#where @#+@pkeyvarStr
select @deleteStr=@#delete @#+@object+@# where @#+@pkeyvarStr
select @selectStr=@#select * from @#+@object+@# where @#+@pkeyvarStr
select @returnPk=@#select @@#+replace(@pkeyStr,@#,@#,@#,@@#)
if charindex(@#IsValid@#,@covNameStr)>0
select @selectFieldStr=@#IsValid=1 and @#
else
select @selectFieldStr=@#@#
select @selectFieldStr=@#select @#+@nameStr+@# from @#+@object+@# where @#+@selectFieldStr+@pkeyvarStr
--declare @saveStr varchar(3000) 作为了输出参数
--select @moneyStr=stuff(@moneyStr,len(@moneyStr),1,char(10)+char(13))
select @saveStr=@#if not exists(@#+@selectStr+@#)@#+char(13)+@insertStr+char(13)+@#else@#+char(13)+@updateStr+char(10)+char(13)+@returnPk
if charindex(@#@IsValid@#,@string)>0
select @string=replace(@string,@#, @IsValid varchar(1)@#,@#@#)
if charindex(@#,@#+char(13)+@# @IsValid@#,@string)>0
select @string=replace(@string,@#,@#+char(13)+@# @IsValid varchar(1)@#,@#@#)
if charindex(@#,@#+char(13)+@# @ModDate@#,@string)>0
select @string=replace(@string,@#,@#+char(13)+@# @ModDate varchar(32)@#,@#@#)
if charindex(@#, @ModDate@#,@string)>0
select @string=replace(@string,@#, @ModDate varchar(32)@#,@#@#)
/*
select @string
select @pkeyParaStr
select @saveStr
--select @insertStr
--select @updateStr
select @deleteStr
--select @selectStr
select @selectFieldStr
*/

原文转自：http://www.ltesting.net