DatabaseProcApplicationCreatedLinks
sybsystemprocssp_add_qpgroup  31 Aug 14Defects Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_add_qpgroup"
6     **
7     ** 17240, "'%1!' is not a valid name."
8     ** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure."
9     ** 17260, "Can't run %1! from within a transaction."
10    ** 17756, "The execution of the stored procedure '%1!' in database
11    **         '%2!' was aborted because there was an error in writing the
12    **         replication log record."
13    ** 18660, "There is already a query plans group named '%1!' in this database."
14    */
15    
16    /*
17    ** sp_add_qpgroup adds a new AP GID - group name 
18    ** association in SYSATTRIBUTES. 
19    */
20    
21    create procedure sp_add_qpgroup
22        @new_name varchar(255)
23    as
24        declare
25            @gid int,
26            @class int,
27            @attribute int,
28            @object_type char(2),
29            @dbname char(255)
30    
31        set nocount on
32    
33        select @dbname = db_name()
34    
35        /* Check to see that the @new_name is valid. */
36        if ((@new_name is not null) and (char_length(@new_name) > 30))
37        begin
38            /*
39            ** 17240, "'%1!' is not a valid name."
40            */
41            raiserror 17240, @new_name
42            return 1
43        end
44    
45        if user_id() != 1 and charindex("sa_role", show_role()) = 0
46        begin
47            /* 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) to execute this procedure." */
48            raiserror 17230
49            return 2
50        end
51    
52        /*
53        ** AP group handling may not be done within a transaction, 
54        ** specifically for sp_add_qpgroup. 
55        **
56        ** Indeed, once a group is created, it can be enabled in 
57        ** DUMP mode and plans start being captured therein - as 
58        ** nested internal Xacts. So if the group creation is part 
59        ** of an user Xact that is rolled back, the APs stay behind, 
60        ** group-less. 
61        **
62        ** sp_drop_qpgroup and sp_rename_qpgroup don't raise 
63        ** the same issue, but for coherence they are subject 
64        ** to the same limitation.
65        */
66        if @@trancount > 0
67        begin
68            /*
69            ** 17260, "Can't run %1! from within a transaction."
70            */
71            raiserror 17260, "sp_add_qpgroup"
72            return 1
73        end
74    
75        /* Don't start a transaction for the user, he does not expect it. */
76        set chained off
77    
78        /* Don't hold long READ locks, the user might not appreciate it. */
79        set transaction isolation level 1
80    
81        exec sp_aux_sargs_qpgroup @class out, @attribute out, @object_type out
82    
83        /*
84        ** This transaction also writes a log record for replicating the
85        ** invocation of this procedure. If logexec() fails, the transaction
86        ** is aborted.
87        **
88        ** IMPORTANT: The name rs_logexec is significant and is used by
89        ** Replication Server.
90        */
91        begin transaction rs_logexec
92    
93        exec sp_aux_get_qpgroup @new_name, @gid out
94    
95        if @gid is not null
96        begin
97            /* 18660, "There is already a query plans group named '%1!' in this database." */
98            raiserror 18660, @new_name
99            rollback transaction rs_logexec
100           return 1
101       end
102   
103       select @gid = isnull(max(object) + 1, 1) from sysattributes
104           holdlock
105       where class = @class
106           and attribute = @attribute
107           and object_type = @object_type
108   
109   
110       insert into sysattributes
111       (class, attribute, object_type, object, object_cinfo)
112       values (@class, @attribute, @object_type, @gid, @new_name)
113   
114       /*
115       ** Write the log record to replicate this invocation
116       ** of the stored procedure.
117       */
118       if (logexec() != 1)
119       begin
120           /*
121           ** 17756, "The execution of the stored procedure
122           **         '%1!' in database '%2!' was aborted
123           **          because there was an error in writing
124           **          the replication log record."
125           */
126           raiserror 17756, "sp_add_qpgroup", @dbname
127           rollback transaction rs_logexec
128           return (1)
129       end
130   
131       commit transaction rs_logexec
132       return 0
133   


exec sp_procxmode 'sp_add_qpgroup', 'AnyMode'
go

Grant Execute on sp_add_qpgroup to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes sybsystemprocs..sysattributes
 MTYP 4 Assignment type mismatch @name: varchar(30) = varchar(255) 93
 MTYP 4 Assignment type mismatch attribute: smallint = int 112
 MTYP 4 Assignment type mismatch class: smallint = int 112
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 105
 QTYP 4 Comparison type mismatch smallint = int 105
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 106
 QTYP 4 Comparison type mismatch smallint = int 106
 MGTP 3 Grant to public sybsystemprocs..sp_add_qpgroup  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MLCH 3 Char type with length>30 char(255) 29
 MNER 3 No Error Check should check return value of exec 81
 MNER 3 No Error Check should check return value of exec 93
 MNER 3 No Error Check should check @@error after insert 110
 MUCO 3 Useless Code Useless Brackets 36
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 128
 QISO 3 Set isolation level 79
 QIWC 3 Insert with not all columns specified missing 10 columns out of 15 111
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_type, attribute}		 105
 MTR1 2 Metrics: Comments Ratio Comments: 60% 21
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 5 = 9dec - 6exi + 2 21
 MTR3 2 Metrics: Query Complexity Complexity: 42 21

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_get_qpgroup  
   reads table sybsystemprocs..sysattributes  
   calls proc sybsystemprocs..sp_aux_sargs_qpgroup  
calls proc sybsystemprocs..sp_aux_sargs_qpgroup  
read_writes table sybsystemprocs..sysattributes  

CALLERS
called by proc sybsystemprocs..sp_import_qpgroup