explain the below stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

explain the below stored procedure

Author

Topic

ramu143
Yak Posting Veteran

64 Posts

Posted - 2008-05-07 : 03:43:13

CREATE procedure r_routeGetCache1
@pLongest varchar(32),
@pLongestCls int,
@pDate datetime,
@pSrcInt varchar(7),
@pSrcIntGroup varchar(64),
@pSrcIntDom varchar(64),
@routeclass int=0 ,
@pCLI varchar(32)='',
@pOperatorGroup int = 0 ,
@isgrade int=0

as
begin
declare @pc varchar(2)
declare @dd int
declare @hh int
declare @mm int

set @pc=left(@pLongest,2)
set @dd=1+(@@datefirst+datepart(dw,@pDate)-2)%7
set @hh=(datepart(hh, @pDate)-0)
set @mm=datepart(mi, @pDate)
set nocount on

--- Azam
select top 1 @routeclass = routecls from r_interface
where [group] = @pSrcIntGroup
and state='I'

if @routeclass is null set @routeclass=0
--- End

--insert
CREATE TABLE #operator_selected (
[routecls] [int] NOT NULL ,
[oprId] [int] NOT NULL ,
[cls] [varchar] (1) NOT NULL ,
[pc] [varchar] (2) NOT NULL
)

insert into #operator_selected(routecls,oprid,cls,pc)
select x.routecls,x.oprid,x.cls,x.pc
from RoutingV3..r20_route_timecls x (nolock),
RoutingV3..r_TimeCls y with (nolock),
RoutingV3..r_interface tit with (nolock),
RoutingV3..r_timecode tco with (nolock),
RoutingV3..r_daycode dco with (nolock)
where x.routecls=0
and tit.id = x.oprid and tit.state = 'I'
and y.tintid =x.oprid and y.cls=x.cls
and y.dc=dco.id and y.tc=tco.id and y.sc=0
and @dd between dco.d1 and dco.d2
and ((24+@hh+isnull(tit.prefixcls,0))%24) * 100 + @mm between tco.h1
and tco.h2-1
and x.pc=@pc

CREATE TABLE #timecls_selected (
[routecls] [int] NOT NULL ,
[oprId] [int] NOT NULL ,
[pc] [varchar] (2) NOT NULL ,
[prefixcode] [varchar] (50) NOT NULL ,
cnt int,
clsA int,
clsP int,
clsO int,
clsW int,
[cls] [varchar] (1)
)

insert into
#timecls_selected(routecls,oprid,pc,prefixcode,cnt,clsA,clsP,clsO,clsW,cls)
select a.routecls, a.oprid, a.pc, max(a.prefixCode) prefixCode,
count(*) cnt,
sum(case when a.cls='A' then 1 else 0 end) clsA,
sum(case when a.cls='P' then 1 else 0 end) clsP,
sum(case when a.cls='O' then 1 else 0 end) clsO,
sum(case when a.cls='W' then 1 else 0 end) clsW, 'Z' cls
from RoutingV3..r20_route12 a(nolock), #operator_selected b
where a.routeCls=@routeclass --b.routeCls
and a.oprId=b.oprId and a.cls=b.cls
and a.pc=b.pc
and left(@pLongest,prefixLen)=a.prefixcode
--- Exclude Artificial
--and not (id<0 and parentId is not null and parentId>0)
group by a.routecls, a.oprid, a.pc

update #timecls_selected
set cls = (case when (clsW>0) and (cnt = clsW) then 'W'
when (clsO>0) and (cnt = clsO) then 'O'
when (clsP>0) and (cnt = clsP) then 'P'
when (clsA>0) and (cnt = clsA) then 'A'
else 'Z' end)

update #timecls_selected
set cls = (select max(a.cls) from RoutingV3..r20_route12 a (nolock)
where a.routecls=#timecls_selected.routecls
and a.oprid = #timecls_selected.oprId
and a.pc=#timecls_selected.pc
and a.prefixCode=#timecls_selected.prefixCode
and ((a.cls='A' and #timecls_selected.clsA<>0) or
(a.cls='P' and #timecls_selected.clsP<>0) or
(a.cls='O' and #timecls_selected.clsO<>0) or
(a.cls='W' and #timecls_selected.clsW<>0))
)
where cls = 'Z'

CREATE TABLE #route (
seqno int identity(10,10),
priority int,
[id] int,
isactive int,
reason int,
exception int,
exceptionCls int,
calcexception int,
ext int,
oprid int,
parentid int,
routecls int,
prefixcode varchar(50),
universe varchar(5),
domain varchar(5),
pdomain varchar(5),
[group] varchar(40),
interface varchar(40),
userinfo varchar(40),
hint varchar(100),
clsorg char(1),
cls char(1),
cost float,
flag int,
rating int,
access varchar(10),
redlist int ,quality float)

if @isgrade =0
begin
insert into #route(isactive,reason, exception, exceptionCls, calcexception,[id],parentid,routecls,oprid,prefixcode,clsorg,cls,cost,
priority,rating,flag,ext,quality)
select
case when x.oprid>0 then x.state
else case when quality=99 then 0 else x.state end
end isactive,
x.reason,
isnull(x.exception,0) exception,
--case when x.oprId>0 then isnull(x.exceptionCls,0) else (case when x.quality=99 then 0 else isnull(x.exceptionCls,0) end) end exceptionCls,
isnull(x.exceptionCls,0) exceptionCls,
case when (isnull(x.exception,0) = 0) then 0
when x.exception > 0 then cast((x.exception+0.5)*10 as int)
else case when x.Id>0 then cast((x.exception-0.5)*10 as int) else 0 end
end [calcException],
x.id, x.parentId, x.routeCls, x.oprId, x.prefixCode, x.clsOrg, x.cls,
x.cost, x.priority, rating, x.flag, x.ext,x.quality
from RoutingV3..r20_route12 x(nolock), #timecls_selected b
where x.routecls=b.routecls and x.oprid=b.oprid and x.pc=b.pc and
x.prefixcode=b.prefixcode and x.cls=b.cls
order by x.cost
end
else
begin
insert into #route(isactive,reason, exception, exceptionCls, calcexception,[id],parentid,routecls,oprid,prefixcode,clsorg,cls,cost,
priority,rating,flag,ext,quality)
select
case when x.oprid>0 then x.state
else case when quality=99 then 0 else x.state end
end isactive,
x.reason,
isnull(x.exception,0) exception,
--case when x.oprId>0 then isnull(x.exceptionCls,0) else (case when x.quality=99 then 0 else 10 end) end exceptionCls,
isnull(x.exceptionCls,0) exceptionCls,
case when (isnull(x.exception,0) = 0) then 0
when x.exception > 0 then cast((x.exception+0.5)*10 as int)
else case when x.Id>0 then cast((x.exception-0.5)*10 as int) else 0 end
end [calcException],
x.id, x.parentId, x.routeCls, x.oprId, x.prefixCode, x.clsOrg, x.cls,
x.cost, x.priority, rating, x.flag, x.ext,x.quality
from RoutingV3..r20_route12 x(nolock), #timecls_selected b
where x.routecls=b.routecls and x.oprid=b.oprid and x.pc=b.pc
and x.prefixcode=b.prefixcode and x.cls=b.cls
order by x.quality,x.cost
end

set nocount on
select top 5
'r20_route12' routeset,
x.seqno priority, x.id, x.isactive, x.reason, x.exception, x.exceptionCls, x.calcexception,
isnull(x.ext,0) ext,
x.routecls, x.prefixcode, y.universe, y.domain, y.pdomain,
y.[group], y.name interface, y.userinfo, y.hint, x.clsOrg, x.cls
timecls, x.cost, x.flag, x.rating,
'11111' access, 0 redlist,x.quality grade
into #topN
from #route x, RoutingV3..r_interface y with (nolock)
where x.isActive=1
and x.oprId=y.id
and @pLongest like x.prefixcode+'%'
and exceptionCls<>0
order by seqno+[calcException], x.cost

select
'r20_route12' routeset,
x.seqno priority, x.id, x.isactive, x.reason, x.exception, x.exceptionCls, x.calcexception,
isnull(x.ext,0) ext,
x.routecls, x.prefixcode, y.universe, y.domain, y.pdomain,
y.[group], y.name interface, y.userinfo, y.hint, x.clsOrg, x.cls
timecls, x.cost, x.flag, x.rating,
'11111' access, 0 redlist,x.quality grade
into #all
from #route x, RoutingV3..r_interface y with (nolock)
where x.oprId=y.id
and @pLongest like x.prefixcode+'%'
order by seqno+[calcException], x.cost

delete #all
from #topN a, #all b
where a.id = b.id

set nocount off

select * from #topN where isActive=1
union all
select * from #all where isActive=1
end

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-07 : 03:51:46

Why, can't you figure out what it does?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-07 : 04:12:27

First make a try to understand it yourself and ask if you cant amke out any portion of it

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-07 : 04:13:26

I can tell you a few things.

1) It is very long, approx 7070 characters.
2) There are no comments in code to help
3) Hard to read since there are no tabs
4) No sample data

I think it is a new design for a coffee machine.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources