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