|
|
|
|
根据表名自动生成INSERT,UPDATE,DELETE,SELECT的SQL语句 |
|
|
|
作者：未知 来源：月光软件站 加入时间：2005-2-28 月光软件站 |
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
*/ | 
|
|
相关文章：相关软件： |
|
本类阅读TOP10