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'[email protected][email protected]+'_'
--head
print '注意：单位换算和货币换算都是在存储过程里实现！'
--sav
select @[email protected]+'sav'
select @[email protected]
select @[email protected]
select @headfct+char(13)[email protected]+char(13)+'as'+char(13)[email protected]+char(10)+char(13)+'go'+char(13)
--tree
select @para=''
select @[email protected]+'tree'
select @[email protected]
select @headfct+char(13)[email protected]+char(13)+'as'+char(13)[email protected]+char(10)+char(13)+'go'+char(13)
--del
select @[email protected]+'del'
select @[email protected]
--test
--select @paravar,charindex('@isvalid',@paravar)
if charindex('isvalid',@selectfieldstr)>0
select @deletestr=replace(replace(@deletestr,'delete','update'),'where','set isvalid=0 where')
select @[email protected]
select @headfct+char(13)[email protected]+char(13)+'as'+char(13)[email protected]+char(10)+char(13)+'go'+char(13)
--back
select @[email protected]+'back'
select @[email protected]
select @headfct+char(13)[email protected]+char(13)+'as'+char(13)[email protected]+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 @[email protected]+' '[email protected] +','
select @[email protected]+'@'[email protected] +','
select @[email protected][email protected]+'[email protected]'[email protected]+','
if len(@updstr)>@i*100
begin
select @[email protected]+char(13)+char(9)+char(9)
select @[email protected]+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 @[email protected]+'@'[email protected]+'__'[email protected]+','
select @typename='varchar'
end
select @[email protected]+' @'[email protected]+' '[email protected]+'('[email protected]+')'+','
if len(@string)>@i*128
begin
select @[email protected]+char(13)
select @[email protected]+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 @[email protected][email protected]+'[email protected]'[email protected]+' and '
select @[email protected][email protected]+','
select @i=charindex('@'[email protected],@string)
if @i>0 select @[email protected]+substring(@string,@i,charindex(',',@string,@i)[email protected])+','
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 @[email protected]+'[email protected]'[email protected]
select @[email protected]
select @i=charindex('@'[email protected],@string)
if @i>0 select @pkeyparastr=substring(@string,@i,charindex(',',@string,@i)[email protected])
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 @[email protected]
if @moneystr<>''
begin
declare @itemstr varchar(50),@itemfield varchar(32),@itemtype varchar(20),@covstr varchar(50)
select @moneystr=','[email protected]
select @moneystr
select @i=1,@itemstr=substring(@moneystr,@i+1,charindex(',',@moneystr,@i+1)[email protected])
while @itemstr<>''
begin
select @itemfield=left(@itemstr,charindex('__',@itemstr)-1)
select @itemtype=right(@itemstr,len(@itemstr)-charindex('__',@itemstr)-1)
select @covstr='cast('[email protected]+' as '[email protected]+')'
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)[email protected])
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 '[email protected]+'('[email protected]+')'+char(13)+char(9)+ 'values '+space(len(@object))+'('[email protected]+')'
else
select @insertstr=space(3)+'begin'+char(13)+char(9)+'declare @count int'+char(13)+char(9)+'select @count=count(*) from '[email protected]+' where substring('[email protected]+',3,4)=convert(varchar(4),getdate(),12)'+char(13)+char(9)+'select @'[email protected]+'=''xx''+convert(varchar(4),getdate(),12)+cast(@count+1 as varchar(16))'+char(13)+char(13)+char(9)+'insert '[email protected]+'('[email protected]+')'+char(13)+char(9)+ 'values '+space(len(@object))+'('[email protected]+')'+char(13)+space(3)+'end'
select @updatestr=char(9)+'update '[email protected]+char(13)+char(9)+'set '[email protected]+char(13)+char(9)+'where '[email protected]
select @deletestr='delete '[email protected]+' where '[email protected]
select @selectstr='select * from '[email protected]+' where '[email protected]
select @returnpk='select @'+replace(@pkeystr,',',',@')
if charindex('isvalid',@covnamestr)>0
select @selectfieldstr='isvalid=1 and '
else
select @selectfieldstr=''
select @selectfieldstr='select '[email protected]+' from '[email protected]+' where '[email protected][email protected]
--declare @savestr varchar(3000) 作为了输出参数
--select @moneystr=stuff(@moneystr,len(@moneystr),1,char(10)+char(13))
select @savestr='if not exists('[email protected]+')'+char(13)[email protected]+char(13)+'else'+char(13)[email protected]+char(10)+char(13)[email protected]
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
*/
