/*
 * SP_EXEC_TABLE.SQL
 * 
 * Description
 * ===========
 * This file contains the stored procedure 'sp_exec_table', which 
 * repeatedly executes a T-SQL statement based on a column values 
 * in an existing table (or view).
 *
 * Installation
 * ============
 * Execute this script using "isql", using a login having 'sa_role'.
 * The stored procedure will be created in the sybsystemprocs database.
 *
 * ASE version compatibility
 * =========================
 * This procedure requires ASE 12.5.0.3 or later becuase it relies on
 * the str_replace() built-in.
 *
 * Parameters
 * ==========
 * sp_exec_table takes the different parameters, depending on the ASE 
 * version for which it is written. Run " sp_exec_table '?' " to get a 
 * description of the parameters.
 *
 * Notes
 * =====
 *
 * - In the SQL command you specify for execution, the placeholder '###' (without quotes)
 *   will be replaced by the evaluated column expression. Also, '##N' (case-insensitive)
 *   will be replaced by the iteration number
 *
 * - sp_exec_table first creates a #temp table with all qualifying rows
 *   and one column containing the evaluated expression you specified.
 *   If your source table is large, this #temp table can potentially 
 *   be large too.
 *
 * - the column expression you specify should result in a single 
 *   column only; ohterwise, an error will result.
 *
 * - the where clause and order-by clause, if specified, should
 *   start with 'WHERE ' and 'ORDER BY ', respectively
 *
 * - sp_exec_table uses execute-immediate to execute the resulting SQL.
 *   This means that you cannot use execute-immediate as part of the 
 *   SQL command you want to execute, since exec-immediate cannot be nested.
 *
 * - sp_exec_table can be executed against a single table only. However, you can
 *   also execute it against a view if more complex logic is needed.
 *
 * - execute 'sp_exec_table' without parameters to see the on-line syntax help.
 *
 *
 * Revision History
 * ================
 * Version 1.0    Sep-2004  First version
 * Version 1.1    Apr-2007  Various enhancements  
 * 
 *
 * Copyright Note & Disclaimer :
 * =============================
 * This software is provided "as is"; there is no warranty of any kind.
 * While this software is believed to work accurately, it may not work 
 * correctly and/or reliably in a production environment. In no event shall  
 * Rob Verschoor and/or Sypron B.V. be liable for any damages resulting 
 * from the use of this software. 
 * You are allowed to use this software free of charge for your own 
 * professional, non-commercial purposes. 
 * You are not allowed to sell or bundle this software or use it for any 
 * other commercial purpose without prior written permission from 
 * Rob Verschoor/Sypron B.V.
 * You may (re)distribute only unaltered copies of this software, which 
 * must include this copyright note, as well as the copyright note in 
 * the header of each stored procedure.
 *
 * Note: All trademarks are acknowledged.
 *
 * Please send any comments, bugs, suggestions etc. to the below email
 * address.
 *
 * Copyright (c) 2004-2007 Rob Verschoor/Sypron B.V.
 *                         P.O.Box 10695
 *                         2501 HR Den Haag
 *                         The Netherlands
 *
 *                         Email: sypron@sypron.nl
 *                         WWW  : http://www.sypron.nl/
 *---------------------------------------------------------------------------- 
 */

set nocount on
go
set flushmessage on
go

use sybsystemprocs
go

-- we need sa_role
if proc_role("sa_role") = 0
begin
   print ""
   print ""
   print " 'sa_role' is required to run this script."
   print " Aborting..."
   print " "
   print " "
   print ""
   set background on  -- terminate this script now
end
go

-- We need to be in 12.5.0.3+ 
-- First test for 12.0 to avoid a syntax error on license_enabled()
if isnull(object_id("master.dbo.sysqueryplans"),99) >= 99  
begin
   print ""
   print ""
   print " This script requires ASE 12.5.0.3 or later."
   print " Aborting..."
   print " "
   print " "
   print ""
   set background on  -- terminate this script now
end
go

-- We need to be in 12.5.0.3+ 
if license_enabled('ASE_XRAY') = NULL
begin
   print ""
   print ""
   print " This script requires ASE 12.5.0.3 or later."
   print " Aborting..."
   print " "
   print " "
   print ""
   select syb_quit()  -- terminate this script now
end
go

----- we're on 12.5.0.3+ -------------------------------------------------------

print ""
print " Installing 'sp_exec_table' ..."
print " For online help information, run ""sp_exec_table '?' "" "
print ""
go

print " Copyright (c) 2004-2007 Rob Verschoor/Sypron B.V."

if object_id("sp_exec_table") <> NULL
begin
   drop proc sp_exec_table
end
go

create proc sp_exec_table
/* Copyright (c) 2004-2007 Rob Verschoor/Sypron B.V. */
   @tabname varchar(200) = NULL,  -- table name
   @col_expr varchar(1000) = NULL, -- column expression
   @p_cmd varchar(16384) = NULL,  -- the command to execute
   @whereclause varchar(1000) = NULL, -- where-clause
   @orderby varchar(1000) = NULL,     -- order-by-clause
   @separator varchar(100) = NULL,     -- separator expr
   @interval varchar(12) = NULL,         -- wait interval between executions ("hh:mm:ss.ccc")
   @max_errors int=3,  -- max.#errors allowed before abort; 0=don't abort
   @progress int=NULL, -- when specified, suppress progress messages
   @debug int=NULL     -- when 1, print details during execution
as
begin
   declare @i int, @step int, @errors int, @rc int, @err int
   declare @cmd varchar(16384)
   declare @start_dt datetime
   declare @now datetime, @now_c varchar(30)
   declare @nr_secs float, @xpersec varchar(10)
   declare @separator2 varchar(100) 
   declare @exec_aborted varchar(30)

   
   set nocount on
   if @p_cmd = "?" or @tabname = NULL or  @tabname = "?"
   begin
      print " "
      print " [sp_exec_table for ASE 12.5]"
      print " Usage: sp_exec_table 'table-name', 'column-expression', 'SQL-expression'"
      print "                     [, 'where-clause' ] [, 'order-by-clause' ] [, 'separator' ] "
      print "                     [, 'interval' ] [, options... ]"
      print " "
      print " Arguments:"
      print " - table-name - the source table based on which a SQL command will be executed."
      print " - column-expression - an expression involving columns of the source table; the"
      print "                       evaluated expression value will be used instead of "
      print "                       '###' when executing the SQL-expression."
      print " - SQL-expression - a quoted string containing the SQL command to be executed"
      print "                    for every row in the source table."
      print "                    Any occurrence of '###' is replaced by the column expression."
      print "                    Any occurrence of '##N' is replaced by the iteration#."
      print " - where-clause [ @whereclause ]"
      print "                  - restricts the qualifying rows from table-name (default=all rows)."
      print "                  When specified, must start with 'WHERE ' (case-insensitive)."
      print " - order-by-clause [ @orderby ]"
      print "                  - defines the order in which the rows are sorted before execution."
      print "                  When specified, must start with 'ORDER BY ' (case-insensitive)."
      print " - separator [ @separator ]"
      print "                  - a SQL statement that's executed between iterations, for example"
      print "                  to print a separator in the output ('##N' is replaced by the "
      print "                  iteration#). By default, no separator is defined."
      print " - interval [ @interval ]  "
      print "                  - a time interval to wait after each execution. Specified"
      print "                  'hh:mm:ss.ccc'. By default, there's no waiting."
      print " "
      print " Options:"
      print " - @max_errors=value - the max. number of allowed errors before sp_exec_table"
      print "                       exits; default=3."
      print " - @progress=value   - when specifying any integer, no progress indications"
      print "                       are printed."
      print " - @debug=value      - when specifying 1, details of each executed command"
      print "                       are printed."
      print " "
      print " Copyright (c) 2004-2007 Rob Verschoor/Sypron B.V."
      print " Visit http://www.sypron.nl"
      print " "
      return
   end

   if @tabname = NULL or @col_expr = NULL or @p_cmd = NULL
   begin
     print "The table name, column expression and SQL expression must all be specified."
     print "Run 'sp_exec_table' for syntax help."
     return 0
   end

   if object_id(@tabname) = NULL
   begin
     print "Table [%1!] not found", @tabname
     return 0
   end

   -- as we're likely to run for some time, identify ourselves
   set clientname "sp_exec_table"
   set clientapplname "sp_exec_table"

   -- initialisations
   select @start_dt = getdate() -- record start time

   select @step = 1 -- default step size for reporting progress

   -----------------------------------------------------------------------

   -- some checks
   if @orderby != NULL
   if upper(substring(ltrim(@orderby), 1, 8)) != 'ORDER BY'
   begin
      print "@orderby should start with 'ORDER BY'"
      return -1
   end

   if @whereclause != NULL
   if upper(substring(ltrim(@whereclause), 1, 5)) != 'WHERE'
   begin
      print "@whereclause should start with 'WHERE'"
      return -1
   end

   -- set up and populate #temp table 

   create table #exectable (cmd varchar(1900), n numeric(18) identity)

   select @cmd = 'insert #exectable (cmd) select ' + @col_expr + ' from ' +
                  @tabname + ' ' + @whereclause + ' ' + @orderby

   if @debug = 1
   begin
      print "cmd=[%1!]", @cmd
   end
 
   exec (@cmd)
   select @rc = @@rowcount, @err = @@error

   if @err != 0 
   begin
      return -1
   end

   if @debug = 1 print "table=[%1!] colum_expression=[%2!] command=[%3!] separator=[%4!]", @tabname, @col_expr, @p_cmd, @separator
   print "%1! rows identified for execution", @rc
   print " "

   if @debug = 1
   begin
      select * from #exectable
   end

   -----------------------------------------------------------------------

   select @errors = 0   -- #errors in executed statement
   select @exec_aborted = ''

   declare c cursor for
      select cmd, n 
      from #exectable
      order by n 
   for read only

   open c

   while 1=1
   begin
      fetch c into @cmd, @i
      if @@sqlstatus > 0 break

      -- Replace '###' with the evaluated column expression
      -- Note that we don't check for exceeding the max. string length.

      -- since we're in 12.5.0.3+, we can use str_replace()
      select @cmd = str_replace(@p_cmd, "###", @cmd)

      -- now execute the SQL command
      if @debug = 1 print "Iteration=%1! cmd=[%2!]", @i, @cmd

      if @separator != NULL
      begin
         -- Replace '##n' with the iteration number
         select @separator2 = str_replace(@separator, "##n", convert(varchar,@i))
         select @separator2 = str_replace(@separator2, "##N", convert(varchar,@i))

         exec(@separator2)
      end

      -- Replace '##n' with the iteration number
      select @cmd = str_replace(@cmd, "##n", convert(varchar,@i))
      select @cmd = str_replace(@cmd, "##N", convert(varchar,@i))

      exec (@cmd)
      if @@error != 0 
      begin 
          print "Error during iteration=%1! cmd=[%2!]", @i, @cmd
          select @errors = @errors + 1

          if @max_errors > 0
          if @errors >= @max_errors
          begin 
             print "Exceeding max.#errors (%1!). Aborting...", @max_errors
             select @exec_aborted = "(execution aborted)"
             break
          end
      end
      
      -- if specified, wait a while between iterations
      if @interval <> NULL
      begin
         waitfor delay @interval
      end

      -- report progress (unless suppressed by @progress != NULL)
      if @progress = NULL
      begin
       if @i%@step = 0  
       begin
          select @now = getdate()
          select @now_c = convert(varchar(11), @now, 106) + " " +
                convert(varchar(11), @now, 108)
          print "[%1!] Executed %2! iterations", @now_c, @i   
       end
      end
   end

   close c

   deallocate cursor c

   if @separator != NULL
   begin
      -- Replace '##n' with the iteration number
      select @separator2 = str_replace(@separator, "##n", "end")
      select @separator2 = str_replace(@separator2, "##N", "end")

      exec(@separator2)
   end

   -- print some statistics 
   select @now = getdate()
   select @now_c = convert(varchar(11), @now, 106) + " " +
                   convert(varchar(11), @now, 108)

   select @i = isnull(@i,0)
   select @nr_secs = datediff(ss, @start_dt, @now) 
   if @nr_secs = 0 select @nr_secs = 1
   select @xpersec = str(@i/(@nr_secs*1.0),8,1)  

   print " "
   print "[%1!] Executed %2! iterations in %3! seconds (%4!/sec)", 
         @now_c, @i, @nr_secs, @xpersec     
   print "table=[%1!]", @tabname
   print "col=  [%1!]", @col_expr
   print "exec= [%1!]", @p_cmd
   print "#errors during execution: %1! %2!", @errors, @exec_aborted
   return (0)
end
go

grant execute on sp_exec_table to public
go
dump tran sybsystemprocs with truncate_only
go

print " Installed  'sp_exec_table' ..."
print " Ready. "
print ""
go

--
-- end
--