sp debug

sp debug method
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   *****************************