/* Job Scheduler Template */
/*
** Messages for sp_jst_mdcache_type_config
**
** 19133, "Procedure %1!, invalid metadata cache type, value must be 0, 1, or 2."
*/

use sybsystemprocs
go
checkpoint
go
set nocount on
go
print 'Creating support Stored Procedure for Sybase Reconfigure Metadata Cache Template'
go
-- Remove sp_jst_get_mdcache_type_config if it exists before attempting to
-- create it
if exists( select 1 from sysobjects
           where name = 'sp_jst_get_mdcache_type_config'
             and type = 'P'
             and (sysstat & 7) = 4 )
    drop proc sp_jst_get_mdcache_type_config
go
/* 
** sp_jst_get_mdcache_type_config
** Version: 1.0
**
** This stored procedure determines the config value for a particular
** metadata cache object type. 
**
** This procedure was developed as part of the ASE Job Scheduler
** templates provided by Sybase. It must be installed on the target
** ASE prior to running a job created from the "Sybase Reconfigure
** Metadata Cache Template"
**
** This stored proc is the called at job execution.  It is a support
** stored procedure and all values for it come from 
** sp_jst_reconf_mdcache
**
** Parameter definitions
** @cache_type  - type of cache object, 0=db,1=indexes,2=objects
** @config      - out param containing config value for mdcache type
*/
create procedure sp_jst_get_mdcache_type_config( @cache_type int = NULL,
                                                 @config int out )
as
begin

    print 'sp_jst_get_mdcache_type_config( @cache_type = %1! )', @cache_type

    declare @sp_name varchar(30)

    --Initializations
    select @sp_name = 'sp_jst_get_mdcache_type_config'

    -- set the metadata cache object string
    if( @cache_type = 0 )
      begin
        --reconfigure db
        select @config = 105
      end
    else
      begin
        if( @cache_type = 1 )
          begin
            --reconfigure indexes
            select @config = 263
          end
        else
          begin
            if( @cache_type = 2 )
              begin
                --reconfigure objects
                select @config = 107
              end
            else
              begin
                -- report invalid cache type
                raiserror 19133
                return -@@error
              end
          end
      end

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