1
2 /*
3 ** sp_sqlrep_check_syntax checks that @parameter is a valid substring
4 ** of @sqlrep.
5 **
6 ** It returns 1 if it is, and 0 if it is not.
7 **
8 ** For example, if @sqlrep = 'udis', then a valid substring of @sqlrep
9 ** is a string in which only characters 'u', 'd', 'i' or 's' may appear.
10 ** They may appear exactly 0 or 1 time.
11 **
12 ** 'ud' is a valid substring, so is 'si'.
13 ** 'uu' is not and neither ' ', nor 'uais' are.
14 */
15 create procedure sp_sqlrep_check_syntax
16 @parameter varchar(20) = NULL, /* parameter to be checked */
17 @sqlrep varchar(4) = NULL, /* 'udis' or 'udi' */
18 @syntax_ok int output /* 1 if ok, 0 otherwise */
19 as
20 begin
21 declare @c char(1)
22 declare @update char(1)
23 declare @delete char(1)
24 declare @inserts char(1)
25 declare @selinto char(1)
26 declare @sqlrep_length int
27 declare @param_count int
28 declare @num_update int
29 declare @num_delete int
30 declare @num_inserts int
31 declare @num_selinto int
32
33 select @sqlrep_length = datalength(@sqlrep)
34 select @syntax_ok = 1
35
36 /*
37 ** If displaying mode only, syntax is OK;
38 ** just return.
39 */
40 if (@parameter is NULL)
41 return
42
43 select @parameter = ltrim(@parameter)
44 select @parameter = rtrim(@parameter)
45
46 /*
47 ** Expedite obviously invalid conditions.
48 ** Single out NULL value for @parameter
49 ** because the NULL value would pass the
50 ** other tests below.
51 */
52 if (datalength(@parameter) > @sqlrep_length) or
53 (@parameter is NULL) or
54 @sqlrep is NULL
55 begin
56 select @syntax_ok = 0
57 end
58 else
59 begin
60 select @update = substring(@sqlrep, 1, 1)
61 select @delete = substring(@sqlrep, 2, 1)
62 select @inserts = substring(@sqlrep, 3, 1)
63
64 /*
65 ** @selinto is NULL if the @sqlrep string
66 ** is 'udi'.
67 ** It is 's' if the @sqlrep string is 'udis'.
68 */
69 select @selinto = substring(@sqlrep, 4, 1)
70
71 select @num_update = 0
72 select @num_delete = 0
73 select @num_inserts = 0
74 select @num_selinto = 0
75
76 select @param_count = 1
77 while (@param_count < datalength(@parameter) + 1)
78 begin
79 select @c = substring(@parameter, @param_count, 1)
80 if (@c = @update)
81 begin
82 select @num_update = @num_update + 1
83 if (@num_update > 1)
84 begin
85 select @syntax_ok = 0
86 break
87 end
88 end
89 else
90 if (@c = @delete)
91 begin
92 select @num_delete = @num_delete + 1
93 if (@num_delete > 1)
94 begin
95 select @syntax_ok = 0
96 break
97 end
98 end
99 else
100 if (@c = @inserts)
101 begin
102 select @num_inserts = @num_inserts + 1
103 if (@num_inserts > 1)
104 begin
105 select @syntax_ok = 0
106 break
107 end
108 end
109 else
110 if (@c = @selinto)
111 begin
112 select @num_selinto = @num_selinto + 1
113 if (@num_selinto > 1)
114 begin
115 select @syntax_ok = 0
116 break
117 end
118 end
119 else
120 begin
121 select @syntax_ok = 0
122 break
123 end
124 select @param_count = @param_count + 1
125 end
126 end
127 return (0)
128 end
129
exec sp_procxmode 'sp_sqlrep_check_syntax', 'AnyMode'
go
Grant Execute on sp_sqlrep_check_syntax to public
go
MGTP 3 Grant to public sybsystemprocs..sp_sqlrep_check_syntax
MTR1 2 Metrics: Comments Ratio Comments: 29%