use sybsystemprocs
go

drop procedure sp_add
go
drop procedure sp_date
go
drop procedure sp_duration
go
drop procedure sp_excld_prnt
go
drop procedure sp_globalexec
go
drop procedure sp_me
go
drop procedure sp_units
go
drop procedure sp_version
go

create procedure sp_add (@NUMBER1   double precision,
                         @NUMBER2   double precision,
                         @NUMBER3   double precision = NULL,
                         @NUMBER4   double precision = NULL,
                         @NUMBER5   double precision = NULL,
                         @NUMBER6   double precision = NULL,
                         @NUMBER7   double precision = NULL,
                         @NUMBER8   double precision = NULL,
                         @NUMBER9   double precision = NULL,
                         @NUMBER10  double precision = NULL,
                         @NUMBER11  double precision = NULL,
                         @NUMBER12  double precision = NULL)  as

   print ' '

   declare @result  double precision

   select @result = @NUMBER1
                    +  @NUMBER2
                    +  isnull (@NUMBER3, 0)
                    +  isnull (@NUMBER4, 0)
                    +  isnull (@NUMBER5, 0)
                    +  isnull (@NUMBER6, 0)
                    +  isnull (@NUMBER7, 0)
                    +  isnull (@NUMBER8, 0)
                    +  isnull (@NUMBER9, 0)
                    +  isnull (@NUMBER10, 0)
                    +  isnull (@NUMBER11, 0)
                    +  isnull (@NUMBER12, 0)

   select 'SUM' = convert (char (22), convert (money, @result), 1)
go
sp_addcomment sp_add, 1, 'Performs addition on a maximum of 12 NUMBERs'
go
sp_procxmode sp_add, anymode
go

create procedure sp_date (@FORMAT_FLAG  varchar (1) = NULL) as

   --  THE DATE FORMAT OPTIONS WHEN @FORMAT_FLAG IS NON-NULL IS BASED UPON
   --  sp__date WRITTEN BY SIMON WALKER, THE SQL WORKSHOP LTD.

   set flushmessage                  on
   set nocount                       on
   set string_rtruncation            on

   print ' '

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    <sp_date> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @i  int

   if (@FORMAT_FLAG IS NULL)
      begin
         if (datepart (hour, getdate ()) < 12)
            begin
               select 'CURRENT DATE/TIME' = convert (varchar (12), getdate (), 107)
                                            + ' ('
                                            + convert (char (8), getdate (), 8)
                                            + ' AM)'
            end
         else
            begin
               select 'CURRENT DATE/TIME' = convert (varchar (12), getdate (), 107)
                                            + ' ('
                                            + convert (char (8), getdate (), 8)
                                            + ' PM)'
            end
      end
   else
      begin
         print '    "FORMAT = convert (char (26), getdate (), OPTION)"'

         print ' '

         select @i = 0

         select OPTION1 = convert (tinyint, @i),
                FORMAT1 = convert (char (26), getdate (), @i),
                OPTION2 = convert (tinyint, @i + 100),
                FORMAT2 = convert (char (26), getdate (), @i + 100)
         into #date

         while (@i < 12)
            begin
               select @i = @i + 1

               insert into #date
                  values (@i,
                          convert (char (26), getdate (), @i),
                          @i + 100,
                          convert (char (26), getdate (), @i + 100))
            end

         select 'OPTION'  = OPTION1,
                'FORMAT'  = FORMAT1,
                'OPTION ' = OPTION2,
                'FORMAT ' = FORMAT2
         from #date
         order by OPTION1
      end
go
sp_addcomment sp_date, 1, 'Prints current date/time in Mon DD, CCYY (HH:MM:SS AM/PM) format'
go
sp_addcomment sp_date, 1, '   If FORMAT_FLAG is non-null then all date formats are displayed'
go
sp_procxmode sp_date, anymode
go

create procedure sp_duration (@TIMESTART  datetime,
                              @TIMEEND    datetime,
                              @DURATION   varchar (14) output) as
   --
   --  RETURN A DURATION IN AN APPROPRIATE DATA-DEPENDENT FORMAT.  ALL
   --  BUT THE FIRST NUMBER WILL BE LEFT ZERO-PADDED.  NOTE THAT THE
   --  MILLISECONDS VALUE IS SOMETIMES SHORT BY 2 MS.
   --     HH:MM:SS
   --     MM:SS
   --     SS.MSS seconds
   --     MSS ms.
   --
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @hh  int,
           @mm  int,
           @ms  int,
           @ss  int

   select @ms       = datediff (ms, @TIMESTART, @TIMEEND),
          @DURATION = NULL

   select @ss = @ms /1000,
          @ms = @ms % 1000

   if (@ss > 0)
      begin
         select @mm = @ss / 60,
                @ss = @ss % 60

         if (@mm > 0)
            begin
               if (@ss > 9)
                  begin
                     select @DURATION = convert (char (2), @ss)
                  end
               else
                  begin
                     select @DURATION = '0'  +  convert (char (1), @ss)
                  end

               select @hh = @mm / 60,
                      @mm = @mm % 60

               if (@mm > 9)
                  begin
                     select @DURATION = convert (char (2), @mm)  +  ':'  +  @DURATION
                  end
               else
                  begin
                     select @DURATION = convert (char (1), @mm)  +  ':'  +  @DURATION
                  end

               if (@hh > 0)
                  begin
                     select @DURATION = convert (varchar (2), @hh)  +  ':'  +  @DURATION
                  end
            end
         else
            begin
               if (@ms > 99)
                  begin
                     select @DURATION = '.'  +  convert (char (3), @ms)
                  end
               else if (@ms > 9)
                  begin
                     select @DURATION = '.0'  +  convert (char (2), @ms)
                  end
               else
                  begin
                     select @DURATION = '.00'  +  convert (char (1), @ms)
                  end

               select @DURATION = convert (varchar (2), @ss)  +  @DURATION  +  ' seconds'
            end
      end
   else
      begin
         select @DURATION = convert (varchar (4), @ms)  +  ' ms.'
      end
go
sp_procxmode sp_duration, anymode
go

create procedure sp_excld_prnt (@TYPE      varchar (17),
                                @EXCLUDE1  varchar (7),
                                @EXCLUDE2  varchar (7) = NULL,
                                @EXCLUDE3  varchar (7) = NULL,
                                @EXCLUDE4  varchar (7) = NULL) as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   declare @print_string  varchar (78),
           @start         tinyint

   select @print_string = @TYPE  +  ' LIKE "'  +  @EXCLUDE1  +  '"'

   if (@EXCLUDE2 IS NOT NULL)
      begin
         select @print_string = @print_string  +  ', "'  +  @EXCLUDE2  +  '"'

         if (@EXCLUDE3 IS NOT NULL)
            begin
               select @print_string = @print_string  +  ', "'  +  @EXCLUDE3  +  '"'

               if (@EXCLUDE4 IS NOT NULL)
                  begin
                     select @print_string = @print_string  +  ', "'  +  @EXCLUDE4  +  '"'
                  end
            end

         select @start = 1 + char_length (@print_string) - charindex (',', reverse (@print_string))

         select @print_string = stuff (@print_string, @start, 2, ' AND ')
      end

   select @print_string = @print_string  +  ' ARE EXCLUDED.'

   print '    %1!', @print_string

   print ' '
go
sp_procxmode sp_excld_prnt, anymode
go

create procedure sp_globalexec (@UTILITY_SP  varchar (30),
                                @ARG1        varchar (30) = NULL,
                                @ARG2        varchar (30) = NULL,
                                @ARG3        varchar (30) = NULL) as
   set ansinull                      on
   set close on endtran              on
   set flushmessage                  on
   set string_rtruncation            on

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    <sp_globalexec> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @procstring  varchar (65),
           @dbname      varchar (30),
           @errorchar   varchar ( 4),
           @srvname     varchar (30)

   declare globalexec_cursor cursor for
      select name
      from master.dbo.sysdatabases
      order by name
   for read only

   open globalexec_cursor

   -- DO AN INITIAL EXECUTION TO CHECK THAT THE UTILITY_SP PARAMETER IS OK.
   -- THIS IS NOT ELEGANT BUT ELIMINATES CHECKS WITHIN THE LOOP.  THERE IS
   -- NO @@sqlstatus CHECK BEFORE THE LOOP SINCE THERE IS A MINIMUM OF FOUR
   -- DATABASES ON A SERVER.

   fetch globalexec_cursor into @dbname

   select @procstring = @dbname  +  '.dbo.'  +  @UTILITY_SP

   execute  @procstring @ARG1, @ARG2, @ARG3

   select @errorchar = convert (varchar (4), @@error)

   if (@errorchar <> '0')
      begin
         print ' '

         print '  sp_globalexec ERROR.  ERROR = %1!.', @errorchar

         print ' '

         print '  UTILITY SP = <%1!>.', @UTILITY_SP

         print ' '

         print '  ARG1 = <%1!>, ARG2 = <%2!>, ARG3 = <%3!>.', @ARG1, @ARG2, @ARG3

         print ' '

         close globalexec_cursor

         deallocate cursor globalexec_cursor

         return  1
      end

   fetch globalexec_cursor into @dbname

   while (@@sqlstatus = 0)
      begin
         select @procstring = @dbname  +  '.dbo.'  +  @UTILITY_SP

         execute  @procstring @ARG1, @ARG2, @ARG3

         fetch globalexec_cursor into @dbname
      end

   if (@@sqlstatus <> 2)
      begin
         select @errorchar = convert (varchar (4), @@sqlstatus)

         print '  CURSOR ERROR.  STATUS = %1!.', @errorchar

         return 1
      end

   close globalexec_cursor

   deallocate cursor globalexec_cursor
go
sp_addcomment sp_globalexec, 1, 'Executes UTILITY PROC in every database with optional arguments ARG1, ARG2, and ARG3'
go
sp_procxmode sp_globalexec, anymode
go

create procedure sp_me as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   print ' '

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    <sp_me> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @idstring    varchar (6),
           @namestring  varchar (40)

   select @namestring = suser_name (origsuid),
          @idstring   = convert (varchar (6), origsuid)
   from master.dbo.sysprocesses
   where spid = @@spid

   if (@namestring IS NOT NULL)
      begin
         select @namestring = ltrim (@namestring  +  ' ('  +  @idstring  +  ')')

         print '    THE set proxy COMMAND HAS BEEN INVOKED FOR LOGIN %1!.',
               @namestring

         print ' '
      end

   select @namestring = name,
          @idstring   = convert (varchar (6), uid)
   from sysusers
   where suid  = suser_id ()
     and uid  <> user_id ()
     and 1    <> user_id ()

   if (@namestring IS NOT NULL)
      begin
         select @namestring = ltrim (@namestring  +  ' ('  +  @idstring  +  ')')

         print '    THE setuser COMMAND HAS BEEN INVOKED FOR USER %1!.',
               @namestring

         print ' '
      end

   select 'LOGIN (spid, suid)' = substring (suser_name ()
                                            +  ' ('
                                            +  convert (varchar (6), @@spid)
                                            +  ', '
                                            +  convert (varchar (6), suser_id ())
                                            +  ')',
                                            1,
                                            24),
          'DATABASE (dbid)'    = substring (db_name ()
                                            +  ' ('
                                            +  convert (varchar (6), db_id ())
                                            +  ')',
                                            1,
                                            18),
          'USER (uid)'         = substring (user_name ()
                                            +  ' ('
                                            +  convert (varchar (6), user_id ())
                                            +  ')',
                                            1,
                                            18),
          'GROUP (gid)'        = substring (ug.name
                                            +  ' ('
                                            +  convert (varchar (6), ug.gid)
                                            +  ')',
                                            1,
                                            14)
   from sysusers ug,
        sysusers uu
   where uu.uid = user_id ()
     and ug.uid = uu.gid
go
sp_addcomment sp_me, 1, 'Displays contextual user identification'
go
sp_procxmode sp_me, anymode
go

create procedure sp_units (@NUMBER  double precision) as
   print ' '

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    <sp_units> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @giga        double precision,
           @kilo        double precision,
           @mega        double precision,
           @parmstring  varchar (255),
           @pgsize      double precision

   if (@NUMBER > 97656249.0)
      begin
         select @parmstring = ltrim (convert (varchar (255), @NUMBER))

         print '    THE PARAMETER VALUE <%1!> IS TOO LARGE FOR sp_units TO USE',
               @parmstring

         print ' '

         print '    THE MAXIMUM VALUE IS 97,656,249.'

         print ' '
      end
   else
      begin
         select @kilo   = convert (double precision, 1024),
                @mega   = convert (double precision, 1048576),
                @giga   = convert (double precision, 1073741824),
                @pgsize = convert (double precision, vE1.number)
         from sybsystemprocs.dbo.usptvalues vE1
         where vE1.type = 'E1'

         select GB  = @NUMBER / @mega,
                MB  = @NUMBER / @kilo,
                P   = (@NUMBER * @kilo) / @pgsize,
                KB  = @NUMBER
         into #units

         insert into #units
            select (@NUMBER * @pgsize) / @giga,
                   (@NUMBER * @pgsize) / @mega,
                   @NUMBER,
                   (@NUMBER * @pgsize) / @kilo

         insert into #units
            select @NUMBER / @kilo,
                   @NUMBER,
                   (@NUMBER * @mega) / @pgsize,
                   @NUMBER * @kilo

         insert into #units
            select @NUMBER,
                   @NUMBER * @kilo,
                   (@NUMBER * @giga) / @pgsize,
                   @NUMBER * @mega

         select 'GigaBytes (GB)' = convert (char (14), convert (money, GB), 1),
                'MegaBytes (MB)' = convert (char (17), convert (money, MB), 1),
                Pages            = convert (char (21), convert (money, P), 1),
                'KiloBytes (KB)' = convert (char (22), convert (money, KB), 1)
         from #units
      end
go
sp_addcomment sp_units, 1, 'Prints NUMBER in equivalent GB, MB, Page, and KB units'
go
sp_procxmode sp_units, anymode
go

create procedure sp_version as
   set ansinull                      on
   set flushmessage                  on
   set string_rtruncation            on

   print ' '

   if (@@trancount = 0)
      begin
         set chained off

         if (@@isolation > 1)
            begin
               set transaction isolation level 1
            end
      end
   else
      begin
         print '    <sp_version> CANNOT BE RUN FROM WITHIN A TRANSACTION.'

         print ' '

         return 1
      end

   declare @break  tinyint

   select @break = charindex (' ', substring (@@version, 45, 255))

      select FIELD = space (9),
             VALUE = space (67)
      where 1 = 2
   union
      select 'Date Run:',
             convert (varchar (12), getdate (), 107)
                + ' ('
                + convert (char (8), getdate (), 8)
                + ' AM)'
      where datepart (hour, getdate ()) < 12
   union
      select 'Date Run:',
             convert (varchar (12), getdate (), 107)
                + ' ('
                + convert (char (8), getdate (), 8)
                + ' PM)'
      where datepart (hour, getdate ()) > 11
   union
      select 'Server:',
             substring (@@servername, 1, 67)
   union
      select 'Version:',
             substring (substring (@@version, 1, 44 + @break), 1, 67)
   union
      select '~        ',
             substring (ltrim (substring (@@version, 45 + @break, 255)), 1, 67)
   order by FIELD
go
sp_addcomment sp_version, 1, 'Displays current date, SQL Server name, and SQL Server version'
go
sp_procxmode sp_version, anymode
go

dump transaction sybsystemprocs with truncate_only
go
