DatabaseProcApplicationCreatedLinks
sybsystemprocssp_showplan  31 Aug 14Defects Dependencies

1     
2     
3     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
4     
5     /* Messages for "sp_showplan"		18359
6     ** 
7     ** 18359, "The spid value must be a positive integer. To look up plans for cached statements, use sp_showplan -1, ."
8     ** 18360, "If the batch id, context id, or statement number is specified, \
9     **         all three must be specified."
10    ** 18361, "Batch id must be non-negative."
11    ** 18362, "Context id must be non-negative."
12    ** 18363, "Statement number must be positive."
13    ** 19611, "The statement id value cannot be NULL or negative."
14    ** 19612, "Could not find a plan for the statement id '%1!'."
15    */
16    
17    create procedure sp_showplan
18    
19        @spid smallint,
20    
21        @batch_id int = NULL output,
22        @context_id int = NULL output,
23        @stmt_num int = NULL output
24    as
25    
26        declare @error int
27        declare @return_value int
28    
29    
30        if @@trancount = 0
31        begin
32            set chained off
33        end
34    
35        set transaction isolation level 1
36        /*
37        ** If @spid is -1, assume that @batch_id is the id for a 
38        ** cached statement.
39        */
40        if (@spid = - 1)
41        begin
42            if (@batch_id is NULL or @batch_id < 0)
43            begin
44                /* 
45                ** 19611, "The statement id value cannot be 
46                ** NULL or negative." 
47                */
48                raiserror 19611
49                return (1)
50            end
51            else
52            begin
53                select @return_value = show_plan(@spid, @batch_id, - 1, - 1)
54                if (@return_value < 0)
55                begin
56                    /*
57                    ** 19612, "Could not find a plan for the
58                    ** statement id '%1!'." 
59                    */
60                    raiserror 19612, @batch_id
61                    return (1)
62                end
63                else
64                    return (0)
65            end
66        end
67    
68        /* 
69        ** sp_showplan is not being used for cached statements,
70        ** the spid value must be a positive integer.
71        */
72        select @error = 0
73        if (@spid is NULL)
74            select @error = 1
75        else if (@spid < 1)
76            select @error = 1
77        if (@error = 1)
78        begin
79            /* 
80            ** 18359, "The spid value must be a positive integer. 
81            ** To look up plans for cached statements, use 
82            ** sp_showplan -1, .
83            */
84            raiserror 18359
85            return (1)
86        end
87    
88    
89        /* 
90        ** Batch id, context id, and statement number must be NULL or 
91        ** none may be NULL.
92        */
93        select @error = 0
94        if (@batch_id * @context_id * @stmt_num is NULL)
95        begin
96            if (@batch_id is not NULL)
97                select @error = 1
98            else if (@context_id is not NULL)
99                select @error = 1
100           else if (@stmt_num is not NULL)
101               select @error = 1
102       end
103       if (@error = 1)
104       begin
105           /* 18360, "If the batch id, context id, or statement number is specified, all three must be specified." */
106           raiserror 18360
107           return (1)
108       end
109   
110   
111       /* 
112       ** Batch id, context id, and statement number are all NULL or none are
113       ** NULL.  If all are NULL, call the builtin function 3 times:
114       ** 	1st to return the batch id, 
115       **	2nd to return the context id, and
116       **	3rd to display the query plan and return the current statement number.
117       */
118       if (@batch_id is NULL)
119       begin
120           /* Pass -1 for unknown values. */
121           select @return_value = show_plan(@spid, - 1, - 1, - 1)
122           if (@return_value < 0)
123               return (1)
124           else
125               select @batch_id = @return_value
126   
127           select @return_value = show_plan(@spid, @batch_id, - 1, - 1)
128           if (@return_value < 0)
129               return (1)
130           else
131               select @context_id = @return_value
132   
133           select @return_value = show_plan(@spid, @batch_id, @context_id, - 1)
134           if (@return_value < 0)
135               return (1)
136           else
137           begin
138               select @stmt_num = @return_value
139               return (0)
140           end
141       end
142   
143   
144       /* 
145       ** Non-NULL parameter values must be non-negative.
146       */
147       if (@batch_id < 0)
148       begin
149           /* 18361, "Batch id must be non-negative." */
150           raiserror 18361
151           return (1)
152       end
153       if (@context_id < 0)
154       begin
155           /* 18362, "Context id must be non-negative." */
156           raiserror 18362
157           return (1)
158       end
159       if (@stmt_num < 1)
160       begin
161           /* 18363, "Statement number must be positive." */
162           raiserror 18363
163           return (1)
164       end
165   
166   
167       /* 
168       ** Display the query plan for the specified statement number.
169       */
170       select @return_value = show_plan(@spid, @batch_id, @context_id, @stmt_num)
171       if (@return_value < 0)
172           return (1)
173       else
174           return (0)
175   
176   

DEFECTS
 MUCO 3 Useless Code Useless Brackets 40
 MUCO 3 Useless Code Useless Brackets 42
 MUCO 3 Useless Code Useless Brackets 49
 MUCO 3 Useless Code Useless Brackets 54
 MUCO 3 Useless Code Useless Brackets 61
 MUCO 3 Useless Code Useless Brackets 64
 MUCO 3 Useless Code Useless Brackets 73
 MUCO 3 Useless Code Useless Brackets 75
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 85
 MUCO 3 Useless Code Useless Brackets 94
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 98
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 128
 MUCO 3 Useless Code Useless Brackets 129
 MUCO 3 Useless Code Useless Brackets 134
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 139
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 151
 MUCO 3 Useless Code Useless Brackets 153
 MUCO 3 Useless Code Useless Brackets 157
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 163
 MUCO 3 Useless Code Useless Brackets 171
 MUCO 3 Useless Code Useless Brackets 172
 MUCO 3 Useless Code Useless Brackets 174
 QISO 3 Set isolation level 35
 MTR1 2 Metrics: Comments Ratio Comments: 45% 17
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 10 = 21dec - 13exi + 2 17
 MTR3 2 Metrics: Query Complexity Complexity: 82 17

DEPENDENCIES
CALLERS
called by proc sybsystemprocs..sp_monitor_procstack