加速你数据库和程序开发的存储过程

如果你是一个数据库工作者，或者是一个代码编写者，你是否为填写那些字段烦恼呢？少还好说，如果达到几十个，你一定会被弄得昏头晕脑，一下就失去了编写代码的快乐。

好了，用以下的方法使你省略了填写字段的烦恼，一下子就能够达到编写代码的性高潮！实在是居家旅游，必备良药，胜过伟哥！

第一步，建立视图！
create VIEW Col AS
select
b.Name ColName,
b.ColID,
c.Name xColtype,
(select Name from systypes where xUserType = c.xType and xType = xUserType) ColType,/*convert user define type to system type*/
b.Length Sizes,
b.Prec Prec,
b.xScale Scale,
convert(bit,b.status&8) Nulls,
a.Name ObjectName,
a.Type ObjectType
from sysobjects A,syscolumns b,systypes c
where a.type in (@#U@#,@#V@#,@#P@#) and a.Id=b.Id and b.Xusertype=c.Xusertype

第一步，建立存储过程！
CREATE PROCEDURE sysgetcol
@objectname Char(80)
AS
declare
@objecttype char(10)
select
@objecttype = objecttype
from COL
where objectname = @objectname

if @@ROWCOUNT = 0
begin
Print @#Internal Error(001):@#
Print @# not found object :@#@#@# + Rtrim(@objectname) +@#@#@#!@#
Return -1
end


select
colname,
colType types,
xColType,
sizes,
prec,
scale,
colid,
Nulls
Into #temp
from COL
where objectname = @objectname
order by colid
--PATINDEX(@#%pattern%@#, expression)

--Script object Structure
if @objecttype = @#U@#
begin
select @#Create Table @# + Rtrim(@objectname) + @# (@#
union all
select @# @# + Rtrim(colname) + @# @# + RTrim(xColType)+
Case xColType when @#Char@# then @#(@#+RTrim(Convert(Char(3),sizes))+@#)@#
when @#Numeric@# then @#(@# + RTrim(Convert(Char(3),Prec)) + @#,@# + RTrim(Convert(Char(3),Scale)) + @#)@#
when @#Varchar@# then @#(@#+RTrim(Convert(Char(3),sizes))+@#) @#
when @#nchar@# then @#(@# + RTrim(Convert(Char(3) ,sizes)) + @#)@#
when @#nvarchar@# then @#(@# + RTrim(Convert(Char(3) ,sizes)) + @#)@#
else @#@#
end +
case nulls when 0 then@# Not Null@# else @#@# end + @# ,@#
from #temp
union all
select @#)@#
end

/*Building select statement*/

select @#CREATE VIEW view_@# + RTrim(@objectname) + @# AS@# + Char(10) + @#select @#
union all
select @# @#+RTrim(colname)+@#,@# from #temp --order by colid
union all
select @#from @#+ RTrim(@objectname)

/******update #temp set sizes=Null where Types<>@#Char@#******/
--bulid procedure parameter
select @#CREATE PROCEDURE @# + RTrim(@objectname) + @#_Update@#
UNION ALL
select
@# @@# + RTrim(colname) + @# @# + RTrim(xColType)+
Case xColType when @#Char@# then @#(@#+RTrim(Convert(Char(3),sizes))+@#) ,@#
when @#Numeric@# then @#(@# + RTrim(Convert(Char(3),Prec)) + @#,@# + RTrim(Convert(Char(3),Scale)) + @#) ,@#
when @#Varchar@# then @#(@#+RTrim(Convert(Char(3),sizes))+@#) ,@#
when @#nchar@# then @#(@# + RTrim(Convert(Char(3) ,sizes)) + @#)@#
when @#nvarchar@# then @#(@# + RTrim(Convert(Char(3) ,sizes)) + @#)@#
else @# ,@#
end
from #temp
--order by colid
UNION ALL
select @#AS@#
/*Building update part*/
UNION ALL
select @# update @# + RTrim(@objectname) + @# set@#
UNION ALL
select @# @#+RTrim(colname)+@# = @@#+RTrim(colname)+@# ,@# from #temp-- order by colid
Union All
select @# where @#
Union All
select @# @#+RTrim(colname)+@# = @@#+RTrim(colname)+@# and@# from #temp-- order by colid
UNION ALL
/*update #temp set sizes=Null*/

/*Building Insert statement*/
select @# if @@ROWCOUNT = 0@#
UNION ALL
select @# insert into @# + Rtrim(@objectname) + @#(@#
UNION ALL
select @# @#+RTrim(colname)+@# ,@# from #temp-- order by colid
UNION ALL
select @# )@#
UNION ALL
select @# values(@#
UNION ALL
select @# @@#+RTrim(colname)+@# ,@# from #temp --order by colid
UNION ALL
select @# )@#

select @# @#+RTrim(colname)+@# = Trim(request("@#+RTrim(colname)+@#"))@# from #temp

select @# @#+RTrim(colname)+@# = Trim(rs("@#+RTrim(colname)+@#"))@# from #temp --order by colid

select @# .parameters(@#+Rtrim(colid)+@#) = @#+ colname from #temp --order by colid
GO


第三步，使用该存储过程！
假设你的数据库里有一个叫做Nta_base_member的表
Create Table Nta_base_member (
m_id bigint Not Null ,
m_type smallint ,
m_state smallint ,
memberid bigint ,
travelco_id bigint
)

打开你的查询分析器，键入
sysgetcol Nta_base_member

然后按ctrl+t，然后按F5，看看查询分析器出现什么东东？

所影响的行数为 5 行）


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Create Table Nta_base_member (
m_id bigint Not Null ,
m_type smallint ,
m_state smallint ,
memberid bigint ,
travelco_id bigint ,
)

（所影响的行数为 7 行）


----------------------------------------------------------------------------------------------------------------------------------
CREATE VIEW view_Nta_base_member AS
select
m_id,
m_type,
m_state,
memberid,
travelco_id,
from Nta_base_member

（所影响的行数为 7 行）


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE Nta_base_member_Update
@m_id bigint ,
@m_type smallint ,
@m_state smallint ,
@memberid bigint ,
@travelco_id bigint ,
AS
update Nta_base_member set
m_id = @m_id ,
m_type = @m_type ,
m_state = @m_state ,
memberid = @memberid ,
travelco_id = @travelco_id ,
where
m_id = @m_id and
m_type = @m_type and
m_state = @m_state and
memberid = @memberid and
travelco_id = @travelco_id and
if @@ROWCOUNT = 0
insert into Nta_base_member(
m_id ,
m_type ,
m_state ,
memberid ,
travelco_id ,
)
values(
@m_id ,
@m_type ,
@m_state ,
@memberid ,
@travelco_id ,
)

（所影响的行数为 34 行）


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
m_id = Trim(request("m_id"))
m_type = Trim(request("m_type"))
m_state = Trim(request("m_state"))
memberid = Trim(request("memberid"))
travelco_id = Trim(request("travelco_id"))

（所影响的行数为 5 行）


----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
m_id = Trim(rs("m_id"))
m_type = Trim(rs("m_type"))
m_state = Trim(rs("m_state"))
memberid = Trim(rs("memberid"))
travelco_id = Trim(rs("travelco_id"))

（所影响的行数为 5 行）


-------------------------------------------------------------------------------------------------------------------------------------------------------
.parameters(1) = m_id
.parameters(2) = m_type
.parameters(3) = m_state
.parameters(4) = memberid
.parameters(5) = travelco_id

（所影响的行数为 5 行）

看到生成的代码，你应该明白什么了吧？呵呵，ctrl+c到你要写的代码里了，爽吧，。。。。。。。。。。。。高潮，射了～


◇ 广告时间：
数据库代码，文档由86fifa编写整理。
NB联盟提供专业级的网站程序开发，数据库开发，收费技术支持、安全顾问服务

联系方式：
QQ组：1019634 （NB联盟）
主页：http://www.54NB.com

原文转自：http://www.ltesting.net