Database	Proc	Application	Created	Links
sybsystemprocs	sp_copy_all_qplans		31 Aug 14	Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_copy_all_qplans"
6     **
7     ** 18639, "There is no query plans group named '%1!' in this database."
8     ** 18656, "Unrecoverable error while copying the query plan %1!. Keeping all plans copied so far and interrupting the copy."
9     ** 18657, "Recoverable error while copying the query plan %1!. Skipping this plan and continuing the copy."
10    ** 18658, "Error while fetching the next query plan to copy. Keeping all plans copied so far and interrupting the copy."
11    */
12    
13    /*
14    ** sp_copy_all_qplans copies all plans in a source group to 
15    ** a destination group. As there could be many of them, rather 
16    ** than having an ACID behavior, the SP attempts to do as much 
17    ** as possible. 
18    ** Note, no learning APs are copied.
19    */
20    
21    create procedure sp_copy_all_qplans
22        @src_group varchar(30),
23        @dest_group varchar(30)
24    as
25        declare
26            @uid int,
27            @src_gid int,
28            @src_id int,
29            @ret int,
30            @rows int
31    
32        set nocount on
33    
34        /* Don't start a transaction for the user, he does not expect it. */
35        if @@trancount = 0
36        begin
37            set chained off
38        end
39    
40        /* Don't hold long READ locks, the user might not appreciate it. */
41        set transaction isolation level 1
42    
43    
44        select @uid = nullif (user_id(), 1)
45    
46        exec sp_aux_get_qpgroup @src_group, @src_gid out
47    
48        if @src_gid is null
49        begin
50            /* 18639, "There is no query plans group named '%1!' in this database." */
51            raiserror 18639, @src_group
52    
53            return 1
54        end
55    
56        /* cursor to scroll through the source APs */
57        declare src_ids cursor
58        for
59        select distinct id
60        from sysqueryplans
61        where
62            uid = isnull(@uid, uid)
63            and gid = @src_gid
64            and type = 100
65    
66        open src_ids
67        fetch src_ids into @src_id
68    
69        while @@sqlstatus = 0
70        begin
71            /* insert each source AP in the destination group */
72            exec @ret = sp_copy_qplan @src_id, @dest_group
73    
74            /*
75            ** @ret == 1 meant no destination group. 
76            ** When there's an @@error, probably we can't 
77            ** go on...
78            */
79            if @ret = 1 or @@error != 0
80            begin
81                /* 18656, "Unrecoverable error while copying the query plan %1!. Keeping all plans copied so far and interrupting the copy." */
82                raiserror 18656, @src_id
83    
84                return 1
85            end
86    
87            /*
88            ** This plan is illegal (key collision, etc)
89            ** but try to continue. 
90            */
91            if @ret != 0
92            begin
93                /* 18657, "Recoverable error while copying the query plan %1!. Skipping this plan and continuing the copy." */
94                raiserror 18657, @src_id
95            end
96    
97            fetch src_ids into @src_id
98        end
99    
100       select @rows = @@rowcount
101   
102       if @@sqlstatus = 1
103       begin
104           /* 18658, "Error while fetching the next query plan to copy. Keeping all plans copied so far and interrupting the copy." */
105           raiserror 18658
106   
107           return 1
108       end
109   
110       return 0
111



exec sp_procxmode 'sp_copy_all_qplans', 'AnyMode'
go

Grant Execute on sp_copy_all_qplans to public
go

DEFECTS
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysqueryplans.csysqueryplans unique clustered (uid, gid, hashkey, id, type, sequence) Intersection: {gid, type}	63
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	64
MGTP 3 Grant to public sybsystemprocs..sp_copy_all_qplans
MGTP 3 Grant to public sybsystemprocs..sysqueryplans
MNER 3 No Error Check should check return value of exec	46
QGWO 3 Group by/Distinct/Union without order by	59
QISO 3 Set isolation level	41
VNRD 3 Variable is not read @rows	100
CRDO 2 Read Only Cursor Marker (has a 'distinct' option)	59
MTR1 2 Metrics: Comments Ratio Comments: 57%	21
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 7 = 9dec - 4exi + 2	21
MTR3 2 Metrics: Query Complexity Complexity: 38	21