DatabaseProcApplicationCreatedLinks
sybsystemprocssp_altermessage  31 Aug 14Defects 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 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    
19        select @parameter = upper(@parameter)
20        select @parameter_value = upper(@parameter_value)
21    
22        /*
23        ** Make sure the user (is the DBO) or (has "sa_role")
24        */
25        if ((user_id() != 1) and (charindex("sa_role", show_role()) = 0))
26        begin
27            /*
28            ** 17230, "Only the System Administrator (SA) or the Database Owner
29            **	   (dbo) may execute this stored procedure."
30            */
31            raiserror 17230
32            return (1)
33        end
34    
35        /*
36        ** Does this message exist?
37        */
38        if (@message_id < 20000)
39        begin
40            if (not exists (select * from master.dbo.sysmessages
41                        where error = @message_id))
42            begin
43                /*
44                ** 17220, "Message number %1! does not exist."
45                */
46                raiserror 17220, @message_id
47                return (1)
48            end
49        end
50        else
51        begin
52            if (not exists (select * from sysusermessages
53                        where error = @message_id))
54            begin
55                /*
56                ** 17220, "Message number %1! does not exist."
57                */
58                raiserror 17220, @message_id
59                return (1)
60            end
61        end
62    
63        /*
64        ** Is Parameter 'WITH_LOG'?
65        */
66        if (@parameter <> 'WITH_LOG')
67        begin
68            /*
69            ** 18371, "The only valid @parameter value is WITH_LOG."
70            */
71            raiserror 18371
72            return (1)
73        end
74    
75        /*
76        ** Is ParameterValue TRUE or FALSE?
77        */
78        if (@parameter_value not in ('TRUE', 'FALSE'))
79        begin
80            /*
81            ** 18372, "The only valid @parameter_value values are TRUE or FALSE."
82            */
83            raiserror 18372
84            return (1)
85        end
86    
87        /*
88        ** Determine the number of messages affected
89        */
90        if (@message_id < 20000)
91        begin
92            select @rows_affected = convert(varchar(16), count(*)) from master.dbo.sysmessages
93            where error = @message_id
94        end
95        else
96        begin
97            select @rows_affected = convert(varchar(16), count(*)) from sysusermessages
98            where error = @message_id
99        end
100       select @msg = @rows_affected + ' Messages altered.'
101   
102       /*
103       ** Turn dlevel bit 7 on or off
104       */
105       if (@parameter_value = 'TRUE')
106       begin
107           if (@message_id < 20000)
108               update master.dbo.sysmessages
109               set dlevel = dlevel | hextoint("0x80")
110               where error = @message_id
111           else
112               update sysusermessages
113               set dlevel = dlevel | hextoint("0x80")
114               where error = @message_id
115           if (@@error = 0)
116           begin
117               if (convert(int, @rows_affected) = 1) PRINT 'Message altered.'
118               if (convert(int, @rows_affected) > 1) PRINT @msg
119           end
120       end
121       else
122       if (@parameter_value = 'FALSE')
123       begin
124           if (@message_id < 20000)
125               update master.dbo.sysmessages
126               set dlevel = dlevel & ~ (hextoint("0x80"))
127               where error = @message_id
128           else
129               update sysusermessages
130               set dlevel = dlevel & ~ (hextoint("0x80"))
131               where error = @message_id
132           if (@@error = 0)
133           begin
134               if (convert(int, @rows_affected) = 1) PRINT 'Message altered.'
135               if (convert(int, @rows_affected) > 1) PRINT @msg
136           end
137       end
138       return (0)
139   


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 108
 MNER 3 No Error Check should check @@error after update 112
 MNER 3 No Error Check should check @@error after update 125
 MNER 3 No Error Check should check @@error after update 129
 MUCO 3 Useless Code Useless Brackets 25
 MUCO 3 Useless Code Useless Brackets 32
 MUCO 3 Useless Code Useless Brackets 38
 MUCO 3 Useless Code Useless Brackets 40
 MUCO 3 Useless Code Useless Brackets 47
 MUCO 3 Useless Code Useless Brackets 52
 MUCO 3 Useless Code Useless Brackets 59
 MUCO 3 Useless Code Useless Brackets 66
 MUCO 3 Useless Code Useless Brackets 72
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 84
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 117
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 132
 MUCO 3 Useless Code Useless Brackets 134
 MUCO 3 Useless Code Useless Brackets 135
 MUCO 3 Useless Code Useless Brackets 138
 MUOT 3 Updates outside transaction 129
 MUPK 3 Update column which is part of a PK or unique index dlevel 109
 MUPK 3 Update column which is part of a PK or unique index dlevel 126
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}		 41
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}		 93
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}		 110
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}		 127
 MSUB 2 Subquery Marker 40
 MSUB 2 Subquery Marker 52
 MTR1 2 Metrics: Comments Ratio Comments: 25% 11
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 14 = 18dec - 6exi + 2 11
 MTR3 2 Metrics: Query Complexity Complexity: 78 11

DEPENDENCIES
PROCS AND TABLES USED
read_writes table sybsystemprocs..sysusermessages  
read_writes table master..sysmessages (1)