模板
教程
環境
性能
功能
管理
根據表名自動生成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 @h
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 |
| 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 */ |
