DatabaseProcApplicationCreatedLinks
sybsystemprocssp_altermessage  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     /* generic/sproc/altermessage	14.2	4/25/91 */
4     
5     /* 
6     ** Messages from sysmessages
7     **
8     ** 18371, "The only valid @parameter value is WITH_LOG."
9     ** 18372, "The only valid @parameter_value values are TRUE or FALSE."
10    */
11    create or replace procedure sp_altermessage
12        @message_id int,
13        @parameter varchar(30),
14        @parameter_value varchar(5)
15    as
16        declare @rows_affected varchar(16)
17        declare @msg varchar(128)
18        declare @dbname varchar(255)
19        declare @dummy int
20        declare @status int
21        declare @status1 int
22        declare @gp_enabled int
23    
24        declare @nullarg varchar(1)
25    
26        select @parameter = upper(@parameter)
27        select @parameter_value = upper(@parameter_value)
28        select @dbname = db_name()
29        select @status1 = 1
30    
31    
32        /*
33        ** Make sure the user (is the DBO) or 
34        ** if gp_enabled = 0 (has "sa_role") 
35        ** if gp_enabled = 1 (has "own database").
36        **	
37        ** Passing NULL for database name because user must be in the database
38        ** where the messasge is being altered.
39        */
40        select @nullarg = NULL
41        execute @status = sp_aux_checkroleperm "dbo",
42            "own database", @dbname, @gp_enabled output
43        if (@status != 0)
44        begin
45            if (@gp_enabled = 0)
46            begin
47                execute @status1 = sp_aux_checkroleperm "sa_role",
48                    @nullarg, @nullarg, @gp_enabled output
49                if (@status1 > 0)
50                begin
51                    select @dummy = proc_role("sa_role")
52                    return 1
53                end
54                /* For Auditing */
55                if (proc_role("sa_role") = 0)
56                    return 1
57            end
58            else
59            begin
60                select @dummy = proc_auditperm("own database",
61                        @status, @dbname)
62                return 1
63            end
64        end
65    
66        /* For Auditing */
67        if (@gp_enabled = 0)
68        begin
69            if (@status1 = 0)
70                select @dummy = proc_role("sa_role")
71        end
72        else
73        begin
74            select @dummy = proc_auditperm("own database", @status, @dbname)
75        end
76    
77    
78        /*
79        ** Does this message exist?
80        */
81        if (@message_id < 20000)
82        begin
83            if (not exists (select * from master.dbo.sysmessages
84                        where error = @message_id))
85            begin
86                /*
87                ** 17220, "Message number %1! does not exist."
88                */
89                raiserror 17220, @message_id
90                return (1)
91            end
92        end
93        else
94        begin
95            if (not exists (select * from sysusermessages
96                        where error = @message_id))
97            begin
98                /*
99                ** 17220, "Message number %1! does not exist."
100               */
101               raiserror 17220, @message_id
102               return (1)
103           end
104       end
105   
106       /*
107       ** Is Parameter 'WITH_LOG'?
108       */
109       if (@parameter <> 'WITH_LOG')
110       begin
111           /*
112           ** 18371, "The only valid @parameter value is WITH_LOG."
113           */
114           raiserror 18371
115           return (1)
116       end
117   
118       /*
119       ** Is ParameterValue TRUE or FALSE?
120       */
121       if (@parameter_value not in ('TRUE', 'FALSE'))
122       begin
123           /*
124           ** 18372, "The only valid @parameter_value values are TRUE or FALSE."
125           */
126           raiserror 18372
127           return (1)
128       end
129   
130       /*
131       ** Determine the number of messages affected
132       */
133       if (@message_id < 20000)
134       begin
135           select @rows_affected = convert(varchar(16), count(*)) from master.dbo.sysmessages
136           where error = @message_id
137       end
138       else
139       begin
140           select @rows_affected = convert(varchar(16), count(*)) from sysusermessages
141           where error = @message_id
142       end
143       select @msg = @rows_affected + ' Messages altered.'
144   
145       /*
146       ** Turn dlevel bit 7 on or off
147       */
148       if (@parameter_value = 'TRUE')
149       begin
150           if (@message_id < 20000)
151               update master.dbo.sysmessages
152               set dlevel = dlevel | hextoint("0x80")
153               where error = @message_id
154           else
155               update sysusermessages
156               set dlevel = dlevel | hextoint("0x80")
157               where error = @message_id
158           if (@@error = 0)
159           begin
160               if (convert(int, @rows_affected) = 1) PRINT 'Message altered.'
161               if (convert(int, @rows_affected) > 1) PRINT @msg
162           end
163       end
164       else
165       if (@parameter_value = 'FALSE')
166       begin
167           if (@message_id < 20000)
168               update master.dbo.sysmessages
169               set dlevel = dlevel & ~ (hextoint("0x80"))
170               where error = @message_id
171           else
172               update sysusermessages
173               set dlevel = dlevel & ~ (hextoint("0x80"))
174               where error = @message_id
175           if (@@error = 0)
176           begin
177               if (convert(int, @rows_affected) = 1) PRINT 'Message altered.'
178               if (convert(int, @rows_affected) > 1) PRINT @msg
179           end
180       end
181       return (0)
182   


exec sp_procxmode 'sp_altermessage', 'AnyMode'
go

Grant Execute on sp_altermessage to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysusermessages sybsystemprocs..sysusermessages
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_altermessage  
 MGTP 3 Grant to public sybsystemprocs..sysusermessages  
 MNER 3 No Error Check should check @@error after update 151
 MNER 3 No Error Check should check @@error after update 155
 MNER 3 No Error Check should check @@error after update 168
 MNER 3 No Error Check should check @@error after update 172
 MUCO 3 Useless Code Useless Brackets 43
 MUCO 3 Useless Code Useless Brackets 45
 MUCO 3 Useless Code Useless Brackets 49
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 67
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 81
 MUCO 3 Useless Code Useless Brackets 83
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 109
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 121
 MUCO 3 Useless Code Useless Brackets 127
 MUCO 3 Useless Code Useless Brackets 133
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 150
 MUCO 3 Useless Code Useless Brackets 158
 MUCO 3 Useless Code Useless Brackets 160
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 165
 MUCO 3 Useless Code Useless Brackets 167
 MUCO 3 Useless Code Useless Brackets 175
 MUCO 3 Useless Code Useless Brackets 177
 MUCO 3 Useless Code Useless Brackets 178
 MUCO 3 Useless Code Useless Brackets 181
 MUOT 3 Updates outside transaction 172
 MUPK 3 Update column which is part of a PK or unique index dlevel 152
 MUPK 3 Update column which is part of a PK or unique index dlevel 169
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
84
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
136
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
153
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
170
 VNRD 3 Variable is not read @dummy 74
 MSUB 2 Subquery Marker 83
 MSUB 2 Subquery Marker 95
 MTR1 2 Metrics: Comments Ratio Comments: 21% 11
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 17 = 23dec - 8exi + 2 11
 MTR3 2 Metrics: Query Complexity Complexity: 103 11

DEPENDENCIES
PROCS AND TABLES USED
read_writes table master..sysmessages (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
read_writes table sybsystemprocs..sysusermessages