sp debug

[b]sp debug method[/b]
create table SPOptimizationTb (
spName varchar(100) not null ,
seq int not null ,
breakpoint int not null ,
consume int not null ,
remark varchar(200) null ,
createDate datetime null
)
lock allpages
on 'default'
go

declare @dbLog bit
declare @breakpoint int
declare @spName varchar(100)
declare @seq int
declare @remark varchar(200)
declare @consume int
declare @logTime datetime

select @dbLog = 0
select @breakpoint = 0 -- breakpoint increase from 1
select @spName = 'SelCommentaryList'

declare @runTime datetime
declare @temTime varchar(200)
set @runTime = getDate()
select @runTime

select @seq = max(seq)+1 from SPOptimizationTb where spName = @spName
if @seq = null
begin
select @seq = 1
end

declare @catIds varchar(16384)
declare @sites varchar(50)
declare @regionCode char(30)
declare @theme int
declare @frDate datetime
declare @toDate datetime
declare @totalRows int
declare @tags varchar(16384)
declare @excludetags varchar(16384)
declare @keyValue varchar(16384)

set @catIds = '|KK%|KM%|'
set @sites = 'GEOEXT'
set @regionCode = 'ALL'
set @theme = -1
set @frDate = '04/20/2011'
set @toDate = '07/19/2011'
set @totalRows = 0
set @tags = ' '
set @excludetags = '|CONTENT_CATEGORY:CC_008|'
set @keyValue = 'EMERGINGMKTS_OVERVIEW_ALL'

-- ************************ record time begin *****************************
select @logTime = getDate()
select @remark = '----#step 1, if else : '
select @consume = datediff(ms,@runTime,@logTime)
select @temTime = convert(varchar(200), @consume)
select @breakpoint = @breakpoint + 1
if @dbLog = 1
insert into SPOptimizationTb values(@spName, @seq, @breakpoint, @consume, @remark, @logTime)

print @remark
print @temTime
select @runTime = getDate()
-- ************************ record time end *****************************