DatabaseProcApplicationCreatedLinks
sybsystemprocssp_drop_qplan  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_drop_qplan"
6     **
7     ** 18636, "There is no query plan with the ID %1! in this database."
8     */
9     
10    /*
11    ** sp_drop_qplan deletes a plan form SYSQUERYPLANS. 
12    ** Straightforward delete by ID.
13    */
14    
15    create procedure sp_drop_qplan
16        @id int
17    as
18        declare @uid int
19    
20        set nocount on
21    
22        /* Don't start a transaction for the user, he does not expect it. */
23        if @@trancount = 0
24        begin
25            set chained off
26        end
27    
28        /* Don't hold long READ locks, the user might not appreciate it. */
29        set transaction isolation level 1
30    
31    
32        select @uid = nullif (user_id(), 1)
33    
34        delete from sysqueryplans
35        where
36            uid = isnull(@uid, uid)
37            and id = @id
38    
39        if @@rowcount = 0
40        begin
41            /* 18636, "There is no query plan with the ID %1! in this database." */
42            raiserror 18636, @id
43    
44            return 1
45        end
46    
47        return 0
48    


exec sp_procxmode 'sp_drop_qplan', 'AnyMode'
go

Grant Execute on sp_drop_qplan 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: {id}		 37
 MGTP 3 Grant to public sybsystemprocs..sp_drop_qplan  
 MGTP 3 Grant to public sybsystemprocs..sysqueryplans  
 MNER 3 No Error Check should check @@error after delete 34
 QISO 3 Set isolation level 29
 MTR1 2 Metrics: Comments Ratio Comments: 43% 15
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 2 = 2dec - 2exi + 2 15
 MTR3 2 Metrics: Query Complexity Complexity: 17 15

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sysqueryplans