在 SQL Server 2000 追鎖定串列

今天為追一台 SQL 2000 伺服器的效能,簡單寫了個 Stored Procedure,沒有 CTE 有些麻煩,只好靠 temp table

create proc spLock @spid int,@lev int=0
as
set @lev=@lev+1
if @spid <> 0
select *,@lev [level] into #temp from master.dbo.sysprocesses
where blocked=@spid
else
insert #temp
select s.*,@lev [level] from master.dbo.sysprocesses s
join (select * from #temp where level=@lev-1) t
on s.blocked=t.spid

if @@rowcount > 0 and @lev<32
exec spLock 0,@lev
if @lev=1
select *,0 [level] from master.dbo.sysprocesses where spid=@spid
union all
select * from #temp
order by [level]
go
exec spLock 111

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: