加速你資料庫和程式開發的預存程序

來源:互聯網
上載者:User
程式|預存程序|資料|資料庫 如果你是一個資料庫工作者,或者是一個代碼編寫者,你是否為填寫那些欄位煩惱呢?少還好說,如果達到幾十個,你一定會被弄得昏頭暈腦,一下就失去了編寫代碼的快樂。

好了,用以下的方法使你省略了填寫欄位的煩惱,一下子就能夠達到編寫代碼的性高潮!實在是居家旅遊,必備良藥,勝過偉哥!

第一步,建立視圖!
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 行)


----------------------------------------------------------------------------------------------------------------------------------------------



相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。