DatabaseProcApplicationCreatedLinks
sybsystemprocssp_exec_SQL  31 Aug 14Defects Dependencies

1     
2     /*
3     ** ***************************************************************************
4     ** sp_exec_SQL
5     **
6     ** Common sproc wrapper to execute immediate a passed-in SQL string. And if
7     ** it fails, raise an error.
8     **
9     ** Parameters:
10    **	@sqlstr		- SQL statement to execute.
11    **	@callerID	- Name of procedure calling us.
12    **	@nrowsaffected	- # of rows affected (output).
13    **
14    ** Returns:
15    **	0	- If no errors occured during execution.
16    **		- The @@error value that was received when an error occured.
17    **		  Client can trap this and do further processing based on
18    **		  the error number.
19    {
20    */
21    create procedure sp_exec_SQL(
22        @sqlstr varchar(4096)
23        , @callerID varchar(30)
24        , @nrowsaffected int = NULL output
25        , @trace tinyint = 0
26    ) as
27        begin
28            declare @retval int
29                , @indent varchar(32)
30                , @sqlstr_len int
31                , @print_maxlen int
32    
33            if (@trace IS NOT NULL) and (@trace != 0)
34            begin
35                select @indent = space(2 * @@nestlevel)
36                    , @sqlstr_len = datalength(@sqlstr)
37                    , @print_maxlen = 1024
38    
39                print " "
40                print "%1!---- Trace sp_exec_SQL Dry-Run: CallerId='%2!' SQL [length=%3!]:"
41                    , @indent, @callerID, @sqlstr_len
42    
43                print "%1!", @sqlstr
44    
45                print " "
46            end
47            set nocount on
48    
49            exec (@sqlstr)
50            select @retval = @@error, @nrowsaffected = @@rowcount
51            if (@retval != 0)
52            begin
53                raiserror 19206, @callerID, @sqlstr
54                if (@trace != 0)
55                begin
56                    select @sqlstr_len = datalength(@sqlstr)
57                    print "Length of SQL Stmt is: %1!", @sqlstr_len
58                    select @sqlstr
59    
60                /*
61                -- Generate the procedural stack trace to see
62                -- where we are coming from. This debugging does
63                -- work, but can produce verbose output. Leave it
64                -- here to be productized/ used on an on-demand basis.
65                --
66                select @indent = "sp_monitor"
67                exec @indent "procstack"
68                */
69    
70                end
71            end
72            set nocount off
73            return @retval
74        end
75    


exec sp_procxmode 'sp_exec_SQL', 'AnyMode'
go

Grant Execute on sp_exec_SQL to public
go
RESULT SETS
sp_exec_SQL_rset_001

DEFECTS
 MPSI 4 Possible SQL Injection @sqlstr 49
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 21
 MGTP 3 Grant to public sybsystemprocs..sp_exec_SQL  
 MUCO 3 Useless Code Useless Brackets in create proc 21
 MUCO 3 Useless Code Useless Begin-End Pair 27
 MUCO 3 Useless Code Useless Brackets 51
 MUCO 3 Useless Code Useless Brackets 54
 QCRS 3 Conditional Result Set 58
 QNAM 3 Select expression has no name @sqlstr 58
 VNRD 3 Variable is not read @print_maxlen 37
 MDYS 2 Dynamic SQL Marker 49
 MRST 2 Result Set Marker 58
 MTR1 2 Metrics: Comments Ratio Comments: 42% 21
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 5 = 4dec - 1exi + 2 21
 MTR3 2 Metrics: Query Complexity Complexity: 23 21

DEPENDENCIES
CALLERS
called by proc sybsystemprocs..sp_spaceusage_object_genoutput  
   called by proc sybsystemprocs..sp_spaceusage_object  
      called by proc sybsystemprocs..sp_spaceusage  
called by proc sybsystemprocs..sp_monitor_list  
called by proc sybsystemprocs..sp_spaceusage_object_init  
   called by proc sybsystemprocs..sp_spaceusage_object  
called by proc sybsystemprocs..sp_monitor_deadlock  
   called by proc sybsystemprocs..sp_monitor  
called by proc sybsystemprocs..sp_spaceusage_object_archdata  
   called by proc sybsystemprocs..sp_spaceusage_object  
called by proc sybsystemprocs..sp_mon_archive_data  
   called by proc sybsystemprocs..sp_mon_archive_monTable  
      called by proc sybsystemprocs..sp_mon_archive_deadlock  
called by proc sybsystemprocs..sp_monitorconfig  
   called by proc sybsystemprocs..sp_sysmon_mdcache  
      called by proc sybsystemprocs..sp_sysmon_analyze  
         called by proc sybsystemprocs..sp_sysmon  
called by proc sybsystemprocs..sp_monitor_deadlock_count_by  
   called by proc sybsystemprocs..sp_monitor_deadlock  
called by proc sybsystemprocs..sp_aux_encr_lookup  
called by proc sybsystemprocs..sp_monitor_enable  
called by proc sybsystemprocs..sp_spaceusage_tranlog_archdata  
   called by proc sybsystemprocs..sp_spaceusage_tranlog  
      called by proc sybsystemprocs..sp_spaceusage  
called by proc sybsystemprocs..sp_mon_archive_deadlock  
called by proc sybsystemprocs..sp_spaceusage_cnvtounit  
   called by proc sybsystemprocs..sp_spaceusage_object  
   called by proc sybsystemprocs..sp_spaceusage_tranlog  
called by proc sybsystemprocs..sp_spaceusage_tranlog_init  
   called by proc sybsystemprocs..sp_spaceusage_tranlog  
called by proc sybsystemprocs..sp_spaceusage_runcmd  
   called by proc sybsystemprocs..sp_spaceusage