如果你是一個(gè)數(shù)據(jù)庫工作者，或者是一個(gè)代碼編寫者，你是否為填寫那些字段煩惱呢？少還好說，如果達(dá)到幾十個(gè)，你一定會(huì)被弄得昏頭暈?zāi)X，一下就失去了編寫代碼的快樂。

好了，用以下的方法使你省略了填寫字段的煩惱，一下子就能夠達(dá)到編寫代碼的性高潮！實(shí)在是居家旅游，必備良藥，勝過偉哥！

第一步，建立視圖！
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

第一步，建立存儲(chǔ)過程！
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


第三步，使用該存儲(chǔ)過程！
假設(shè)你的數(shù)據(jù)庫里有一個(gè)叫做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，看看查詢分析器出現(xiàn)什么東東？

所影響的行數(shù)為 5 行）


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

（所影響的行數(shù)為 7 行）


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

（所影響的行數(shù)為 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 ,
)

（所影響的行數(shù)為 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"))

（所影響的行數(shù)為 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"))

（所影響的行數(shù)為 5 行）


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

（所影響的行數(shù)為 5 行）

看到生成的代碼，你應(yīng)該明白什么了吧？呵呵，ctrl+c到你要寫的代碼里了，爽吧，。。。。。。。。。。。。高潮，射了～


◇ 廣告時(shí)間：
數(shù)據(jù)庫代碼，文檔由86fifa編寫整理。
NB聯(lián)盟提供專業(yè)級的網(wǎng)站程序開發(fā)，數(shù)據(jù)庫開發(fā)，收費(fèi)技術(shù)支持、安全顧問服務(wù)

聯(lián)系方式：
QQ組：1019634 （NB聯(lián)盟）
主頁：http://www.54NB.com