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