一直没有好好写过存储过程,今天一个看起来很简单的问题,结果写了一下午的存储过程
要做的是查询若干个表,然后对结果做些修改,最后返回这些结果。本来以为存储返回结果可以写return table之类的,后来才发现都是使用select语句来返回的。这样只能先产生一个临时表来保存最初查询结果,然后遍历做修改,最后返回临时表内容,并删除该表。
CREATE procedure sp_func
as
begin
create table #tmpTable
(
/*
*/
)
declare curTable cursor for
--
open curTable
while 1=1
begin
fetch next from curTable into --
if @@fetch_status <> 0
break
/*
*/
end
close curTable
deallocate curTable
select * from #tmpTable
drop table #tmpTable
end
go
大致上就是这样,对熟手来说这根本不算什么,是最常用的方法。
![](/Emoticons/emdgust.gif)
要做的是查询若干个表,然后对结果做些修改,最后返回这些结果。本来以为存储返回结果可以写return table之类的,后来才发现都是使用select语句来返回的。这样只能先产生一个临时表来保存最初查询结果,然后遍历做修改,最后返回临时表内容,并删除该表。
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/dot.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/dot.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/dot.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/dot.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)
![](/Images/OutliningIndicators/None.gif)