DatabaseProcApplicationCreatedLinks
sybsystemprocssp_query_tuning  31 Aug 14Defects Dependencies

1     
2     /*
3     ** Generated by spgenmsgs.pl on Tue Dec 19 13:25:48 2006 
4     */
5     /*
6     ** raiserror Messages for query_tuning [Total 3]
7     **
8     ** 19421, "Syntax error. '%1!' should be a string representation of an integer, such as '100'."
9     ** 19617, "Syntax error. Extra arguments passed to '%1!' command. Run: sp_query_tuning 'help', '%1!' for usage information."
10    ** 19618, "Unsupported command '%1!'. Check command and retry sp_query_tuning."
11    */
12    /*
13    ** sp_getmessage Messages for query_tuning [Total 0]
14    */
15    /*
16    ** End spgenmsgs.pl output.
17    */
18    
19    /*
20    ** sp_query_tuning
21    **
22    **	Top-level user-interface procedure for query_tuning optimizer procedures.
23    **
24    **	This procedure is the command-control for the entire interface. It
25    **	receives the command from the user, parses it, and then calls other
26    **	sub-procs to execute on each command.
27    **
28    ** Returns:
29    **	0	- All checks are ok. Execution is ok.
30    **	1	- Some errors (arguments, usage) or permission(s) issues.
31    */
32    create procedure sp_query_tuning
33        @command varchar(30) = NULL
34        , @arg1 varchar(30) = NULL
35        , @arg2 varchar(600) = NULL
36    as
37    
38        declare @retval int /* From called sub-procedure.*/
39            , @param1 int
40            , @param2 int
41            , @param_len int
42            , @loopcount int
43            , @param_bit char(1)
44    
45        select @retval = 1 /* Expect failure. */
46    
47        set transaction isolation level 1
48        if @@trancount = 0
49        begin
50            set chained off
51        end
52        set nocount on
53    
54        /*
55        ** Allow non-sa users (or even non-dbo users) to run without any
56        ** arguments, at least so that the dbo can get some help info before
57        ** working on a particular command. (It is difficult to further restrict
58        ** this to regular users when we have absolutely no arguments.)
59        ** Remaining permissions checks will be done shortly hereafter.
60        */
61        if (@command IS NULL)
62        begin
63            exec @retval = sp_help_query_tuning
64            return @retval
65        end
66    
67        /*
68        ** ****************************************************************
69        ** No top-level permissions checks are being done in this procedure
70        ** as the permissions vary depending on the command being run and
71        ** granularity of the operation. Permission checking will be
72        ** implemented by each sub-command's procedure.
73        ** ****************************************************************
74        */
75        if (@command = 'flush')
76        begin
77            if (@arg1 is NOT NULL or @arg2 is NOT NULL)
78            begin
79                /* Report on extra arguments, and fail.*/
80                raiserror 19617, @command
81                return 1
82            end
83    
84            exec @retval = sp_flush_query_tuning
85        end
86        else if (@command = 'drop')
87        begin
88            select @loopcount = 0
89            select @param_len = char_length(@arg1)
90            while (@loopcount <= @param_len)
91            begin
92                select @param_bit = substring(@arg1, @loopcount, 1)
93                if NOT (@param_bit >= '0' and @param_bit <= '9')
94                begin
95                    raiserror 19421, '@arg1'
96                    return 1
97                end
98                select @loopcount = @loopcount + 1
99            end
100   
101           select @loopcount = 0
102           select @param_len = char_length(@arg2)
103           while (@loopcount <= @param_len)
104           begin
105               select @param_bit = substring(@arg2, @loopcount, 1)
106               if NOT (@param_bit >= '0' and @param_bit <= '9')
107               begin
108                   raiserror 19421, '@arg2'
109                   return 1
110               end
111               select @loopcount = @loopcount + 1
112           end
113   
114           select @param1 = convert(integer, @arg1)
115           select @param2 = convert(integer, @arg2)
116           exec @retval = sp_drop_query_tuning @param1, @param2
117       end
118       else if (@command = 'show')
119       begin
120           if (@arg2 is NOT NULL)
121           begin
122               /* Report on extra arguments, and fail.*/
123               raiserror 19617, @command
124               return 1
125           end
126   
127           exec @retval = sp_show_query_tuning @arg1
128       end
129       else if (@command = 'help')
130       begin
131           if (@arg2 is NOT NULL)
132           begin
133               /* Report on extra arguments, and fail. */
134               raiserror 19617, @command
135               return 1
136           end
137           exec @retval = sp_help_query_tuning @arg1
138       end
139       else
140       begin
141           raiserror 19618, @command
142           return 1
143       end
144   
145       set nocount off
146   
147       return @retval
148   


exec sp_procxmode 'sp_query_tuning', 'AnyMode'
go

Grant Execute on sp_query_tuning to public
go
DEFECTS
 MGTP 3 Grant to public sybsystemprocs..sp_query_tuning  
 MNER 3 No Error Check should check return value of exec 63
 MNER 3 No Error Check should check return value of exec 84
 MNER 3 No Error Check should check return value of exec 116
 MNER 3 No Error Check should check return value of exec 127
 MNER 3 No Error Check should check return value of exec 137
 MUCO 3 Useless Code Useless Brackets 61
 MUCO 3 Useless Code Useless Brackets 75
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 86
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 131
 QISO 3 Set isolation level 47
 MTR1 2 Metrics: Comments Ratio Comments: 47% 32
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 10 = 16dec - 8exi + 2 32
 MTR3 2 Metrics: Query Complexity Complexity: 65 32

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_show_query_tuning  
   reads table sybsystemprocs..sysqueryplans  
calls proc sybsystemprocs..sp_drop_query_tuning  
   read_writes table sybsystemprocs..sysqueryplans  
calls proc sybsystemprocs..sp_flush_query_tuning  
calls proc sybsystemprocs..sp_help_query_tuning  
   calls proc sybsystemprocs..sp_getmessage  
      calls proc sybsystemprocs..sp_validlang  
         reads table master..syslanguages (1)  
      reads table sybsystemprocs..sysusermessages  
      reads table master..sysmessages (1)  
      reads table master..syslanguages (1)