MSSQL编程：依据表名主动天生INSERT,UPDATE,DELETE...

对于insert操作，只需要把event_type改成DELETE_ROWS_EVENT；对于delete操作，改成WRITE_ROWS_EVENTselect|语句TBPROC

CREATEProcedureTbProc
@modelvarchar(2),@pagenamevarchar(32),@objectvarchar(32),@autoFieldvarchar(32)=null
as
setnocounton
select@model=upper(@model)
select@pagename=lower(@pagename)
select@object=upper(@object)
declare@headvarchar(100),@headfctvarchar(105),@paravarchar(1500),@contentvarchar(5000)
declare@paravarvarchar(2000),@saveStrvarchar(3000),@deleteStrvarchar(500),@selectFieldStrvarchar(1000),@returnPkvarchar(250),@pkeyStrvarchar(500),@pkeyParaStrvarchar(500)
select@head=,@headfct=,@para=,@content=
execTbField@object,@autofield,@paravaroutput,@saveStroutput,@deleteStroutput,@selectFieldStroutput,@pkeyStroutput,@pkeyParaStroutput
select@head=createprocedurep+@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)
ifcharindex(IsValid,@selectFieldStr)>0
select@deleteStr=replace(replace(@deleteStr,delete,update),where,setIsValid=0where)
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

CREATEprocedureTbField
@objectvarchar(32),@autofieldvarchar(32),@stringvarchar(2000)output,@saveStrvarchar(3000)output,@deleteStrvarchar(500)output,@selectFieldStrvarchar(1000)output,@pkeyStrvarchar(500)output,@pkeyParaStrvarchar(500)output
as
setnocounton
select@object=upper(@object)
declare@nameStrvarchar(1000),@varStrvarchar(1000),@updStrvarchar(1500),@pkeyvarStrvarchar(500)--,@pkeyParaStrvarchar(150),@pkeyStrvarchar(250)作为了输入参数
select@string=,@nameStr=,@varStr=,@updStr=,@pkeyvarStr=,@pkeyStr=,@pkeyParaStr=
declare@moneyStrvarchar(500)
select@moneyStr=
declare@ismallint
select@i=1
declare@fieldtbtable(pkintidentity,fieldvarchar(32))
declare@attrtbtable(fieldvarchar(32),typenamevarchar(32),lengthsmallint)
declare@fieldvarchar(32)
declarecurfieldcursorfor
selectnamefromsyscolumnswhereid=object_id(@OBJECT)
opencurfield
fetchnextfromcurfieldinto@field
while@@fetch_status=0
begin
insert@fieldtb(field)values(@field)
select@nameStr=@nameStr++@field+,
select@varStr=@varStr+@+@field+,
select@updStr=@updStr+@field+=@+@field+,
iflen(@updStr)>@i*100
begin
select@updStr=@updStr+char(13)+char(9)+char(9)
select@i=@i+1
end
fetchnextfromcurfieldinto@field
end
closecurfield
deallocatecurfield
insert@attrtb
selectdistinctc.name,replace(replace(d.type_name,identity,),(),),c.length
fromsyscolumnsc
innerjoinmaster.dbo.spt_datatype_infodonc.xtype=d.ss_dtype
wherec.id=object_id(@OBJECT)
-----select*from@attrtb--测试
select@i=1
declare@typenamevarchar(32),@lengthvarchar(5)
declarerecordcursorfor
selecta.*
from@fieldtbfinnerjoin@attrtbaonf.field=a.field
orderbyf.pk
openrecord
fetchnextfromrecordinto@field,@typename,@length
while@@fetch_status=0
begin
if@typenamenotin(varchar,nvarchar,char,nchar,text,ntext)
begin
select@length=case@typenamewhensmalldatetimethen10
whendatetimethen32
whenbitthen1
else16
end
if@typenamein(money,smallmoney)
select@moneyStr=@moneyStr+@+@field+__+@typename+,
select@typename=varchar
end
select@string=@string+@+@field++@typename+(+@length+)+,
iflen(@string)>@i*128
begin
select@string=@string+char(13)
select@i=@i+1
end
fetchnextfromrecordinto@field,@typename,@length
end
closerecord
deallocaterecord
--Aboutpkeys
declare@pkeytbtable(fieldvarchar(32))
insert@pkeytb
selectc.name
fromsyscolumnscinnerjoinsysindexesionc.id=i.id
wherec.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(selectcount(*)from@pkeytb)>1
begin
declarecurpkeyscursorfor
selectfieldfrom@pkeytb
opencurpkeys
fetchnextfromcurpkeysinto@field
while@@fetch_status=0
begin
select@pkeyvarStr=@pkeyvarStr+@field+=@+@field+and
select@pkeyStr=@pkeyStr+@field+,
select@i=charindex(@+@field,@string)
if@i>0select@pkeyParaStr=@pkeyParaStr+substring(@string,@i,charindex(,,@string,@i)-@i)+,
fetchnextfromcurpkeysinto@field
end
closecurpkeys
deallocatecurpkeys
select@pkeyvarStr=left(@pkeyvarStr,len(@pkeyvarStr)-3)
select@pkeyStr=left(@pkeyStr,len(@pkeyStr)-1)
select@pkeyParaStr=left(@pkeyParaStr,len(@pkeyParaStr)-1)
end
elseif(selectcount(*)from@pkeytb)=1
begin
select@field=fieldfrom@pkeytb
select@pkeyvarStr=@field+=@+@field
select@pkeyStr=@field
select@i=charindex(@+@field,@string)
if@i>0select@pkeyParaStr=substring(@string,@i,charindex(,,@string,@i)-@i)
end
ifright(@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)
ifright(@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@covNameStrvarchar(1000)
select@covNameStr=@nameStr
if@moneyStr
BEGIN
declare@itemStrvarchar(50),@itemfieldvarchar(32),@itemtypevarchar(20),@covStrvarchar(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
elseselect@itemStr=substring(@moneyStr,@i+1,charindex(,,@moneyStr,@i+1)-@i-1)
end
END
--------------------
declare@insertStrvarchar(2000),@updateStrvarchar(2000),@selectStrvarchar(500)--,@deleteStrvarchar(500)作为了输入参数
declare@returnPkvarchar(250)--,@selectFieldStrvarchar(1000)作为了输入参数
if@autofieldisnull
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@countint+char(13)+char(9)+select@count=count(*)from+@object+wheresubstring(+@autofield+,3,4)=convert(varchar(4),getdate(),12)+char(13)+char(9)+select@+@autofield+=XX+convert(varchar(4),getdate(),12)+cast(@count+1asvarchar(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,,,,@)
ifcharindex(IsValid,@covNameStr)>0
select@selectFieldStr=IsValid=1and
else
select@selectFieldStr=
select@selectFieldStr=select+@nameStr+from+@object+where+@selectFieldStr+@pkeyvarStr
--declare@saveStrvarchar(3000)作为了输入参数
--select@moneyStr=stuff(@moneyStr,len(@moneyStr),1,char(10)+char(13))
select@saveStr=ifnotexists(+@selectStr+)+char(13)+@insertStr+char(13)+else+char(13)+@updateStr+char(10)+char(13)+@returnPk
ifcharindex(@IsValid,@string)>0
select@string=replace(@string,,@IsValidvarchar(1),)
ifcharindex(,+char(13)+@IsValid,@string)>0
select@string=replace(@string,,+char(13)+@IsValidvarchar(1),)
ifcharindex(,+char(13)+@ModDate,@string)>0
select@string=replace(@string,,+char(13)+@ModDatevarchar(32),)
ifcharindex(,@ModDate,@string)>0
select@string=replace(@string,,@ModDatevarchar(32),)
/*
select@string
select@pkeyParaStr
select@saveStr
--select@insertStr
--select@updateStr
select@deleteStr
--select@selectStr
select@selectFieldStr
*/因此我们看到，这些信息足够让我们对单个操作实现“逆操作”。

可以动态传入参数，省却了动态SQL的拼写。

不好!如果出了错;不好调试;不好处理!其实web开发将代码分为3层:web层;业务逻辑层和数据访问层;一般对数据库的操作都在数据访问层来做;这样便于调试和维护!而且将来如果是换了数据库的话;你只需要改数据层的代码;其他层的基本可以不变!要是你在jsp中直接调用sql数据库;那么如果换了数据库呢?岂不都要改?如果报了异常呢?怎么做异常处理?

只能告诉你，学好数据库语言和原理，多见识几种数据库软件，比一棵树上吊死要好。

对于微软系列的东西除了一遍遍尝试还真没有太好的办法

在select语句中可以使用groupby子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息，另外，可以使用having子句限制返回的结果集。

你可以简单地认为适合的就是好，不适合就是不好。

对递归类的树遍历很有帮助。个人感觉这个真是太棒了！阅读清晰，非常有时代感。

比如，MicrosoftSQLServer2008的某一个版本可以满足现在的这个业务的需要，而且价格还比Oracle11g要便宜，那么这一产品就是适合的。