根据表名自动生成INSERT,UPDATE,DELETE,SELECT的SQL语代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】根据表名自动生成INSERT,UPDATE,DELETE,SELECT的SQL语
作者:Dezai.CN / 发布于2013/9/18/ 1035
根据表名自动生成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
	*/
评论列表
本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。
试试其它关键字
　自动生成ISQL语句　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)