/* Job Scheduler Template */
/*
** Messages for sp_jst_get_datestr
**
** 19048, "Procedure %1!, parameter %2!, was not supplied, NULL or empty."
** 19123, "%1! value of %2! is invalid."
*/

use sybsystemprocs
go
checkpoint
go
set nocount on
go
print 'Generic stored procedure used by Sybase Job Scheduler Template jobs'
go
-- Remove the sp_jst_get_datestr if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_get_datestr'
             and type = 'P'
             and (sysstat & 7) = 4 )
    drop proc sp_jst_get_datestr
    print 'sp_jst_get_datestr dropped'
go
/*
** sp_jst_get_datestr
** Version: 1.0
**
** This procedure takes a value indicating which data format to use
** and returns a date string in that format. A return value of less
** than 0 indicates an error.
**
** This stored procedure were developed as part of the ASE Job Scheduler
** templates provided by Sybase. It must be installed on the target
** ASE prior to running jobs created from Sybase templates that 
** specify its use.
**
** This stored proc is the called at job execution.  It is a support
** stored procedure and all values for it come from other stored
** procedures.
**
** Parameter definitions
** @date_fmt - value from ASE JS Plugin Templates window indicating
**             the format value. Note: this is not the ASE value
**             for date formats.
** @date_str - out parameter to hold the date string
*/
create procedure sp_jst_get_datestr( @date_fmt int = NULL,
                                     @date_str varchar(25) = NULL out )  
as
begin

    print 'sp_jst_get_datestr(date_fmt = %1!)', @date_fmt

    declare @ase_fmt int
          , @err int
          , @sp_name varchar(30)

    --initializations
    select @sp_name = 'sp_jst_get_datestr'

    -- verify format value supplied, should never happen from templates
    if( @date_fmt = NULL )
      begin
          raiserror 19048, @sp_name, 'Date Format'
          return -@@error
      end

    -- map template format values to ASE format values
    -- Template          ASE
    --    0              no date
    --    1              0
    --    2		 2
    --    3              4
    --    4              5
    --    5              10
    --    6              12

    if( @date_fmt = 1 )
      begin
        select @ase_fmt = 0
      end
    else
      begin
        if( @date_fmt = 2 )
          begin
            select @ase_fmt = 2
          end
        else
          begin
            if( @date_fmt = 3 )
              begin
                select @ase_fmt = 4
              end
            else
              begin
                if( @date_fmt = 4 )
                  begin
                    select @ase_fmt = 5
                  end
                else
                  begin
                    if( @date_fmt = 5 )
                      begin
                        select @ase_fmt = 10
                      end
                    else
                      begin
                        if( @date_fmt = 6 )
                          begin
                            select @ase_fmt = 12
                          end
                        else
                          begin
                            -- invalid format type
                            raiserror 19123, 'Date Format', @date_fmt
                            return -19123
                          end
                      end
                  end
              end
          end
      end

    select @date_str = convert(varchar(25), getdate(), @ase_fmt )

    --remove any spaces from the date string
    if( @ase_fmt = 0 )
      begin
        declare @end_pos int
              , @temp_str varchar(25)
        select @end_pos = charindex(' ', @date_str )
        while @end_pos > 0
          begin
            select @temp_str = @temp_str + substring(@date_str,1,@end_pos-1)
            -- remove the db we are currently processing
            select @date_str = stuff(@date_str,1,@end_pos,NULL)
            select @end_pos = charindex(' ', @date_str )
          end
        --remove the leading space if any
        select @date_str = ltrim(@date_str)
        select @date_str = @temp_str + @date_str
      end

    return 1
end
go
if exists( select 1 from sysobjects
           where name = 'sp_jst_get_datestr'
             and type = 'P'
             and (sysstat & 7) = 4 )
begin
    print 'sp_jst_get_datestr has been created'
    grant execute on sp_jst_get_datestr to public
end
go
