IDC资讯中心
如果你是一个数据库工作者，或者是一个代码编写者，你是否为填写那些字段烦恼呢？少还好说，如果达到几十个，你一定会被弄得昏头晕脑，一下就失去了编写代码的快乐。
好了，用以下的方法使你省略了填写字段的烦恼，一下子就能够达到编写代码的性高潮！实在是居家旅游，必备良药，胜过伟哥！
第一步，建立视图！
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
