 |
|
|
 首页 |
名片设计 CorelDRAW Illustrator AuotoCAD Painter 其他软件 Photoshop Fireworks Flash |
|
假如你是一个数据库工作者，或者是一个代码编写者，你是否为填写那些字段烦恼呢？少还好说，假如达到几十个，你一定会被弄得昏头晕脑，一下就失去了编写代码的快乐。 第一步，建立视图！ 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到你要写的代码里了 返回类别: 教程 上一教程: BBS的数据结构和存储过程(二) 下一教程: ASP文章系统的解决方案 您可以阅读与"加速你数据库和程序开发的存储过程"相关的教程: · 用存储过程实现ASP对数据库访问 · 一个简朴的存储过程数据分页 · 利用SQL的存储过程实现DOS命令的ASP程序 · 人民银行网站建设中数据库存储过程的调用 · ASP列出SQL数据库中(PUBS数据库)存储过程及其例子 |
  |
| 快精灵印艺坊 版权所有 |
首页 会员中心在线印刷在线编辑付款方式索取样品设计指南连锁门店
|
||
