Hi,

I have a stored procedure(ex: log_message) on various databases that is used for logging errors to tables. This proc is nested/executed as part of running other stored procs(ex: Grant_permissions) to log the errors that are produced while running them.

I have a situation where I have to run the  log_message based on the a @environment  parameter value specified in Grant_permissions proc.

Grant_permissions  proc looks some thing like this

CREATE PROCEDURE [permissions_grant]
	@role_name                     varchar(200) = NULL,
	@role_type_code                char(1)      = NULL,
	@log_header_key                INT          = NULL,
	@debug_ind                     char(1)      = 'n',
	@database_name                 Varchar(100)
	
WITH RECOMPILE
AS
Begin
SET NOCOUNT ON 
DECLARE @tran_name                    varchar(100),
        @process_name_text            varchar(100),
        @procedure_name               varchar(255),
        @grant_text                   varchar(max),
        @error                        int,
		@database_type_raw_or_clean    Varchar(200),
		@environment                   VARCHAR (50) 
SELECT @procedure_name             = OBJECT_SCHEMA_NAME( @@PROCID ) + '.' + OBJECT_NAME( @@PROCID ),
       @process_name_text          = OBJECT_NAME( @@PROCID ),
       @error                      = 0,
       @database_type_raw_or_clean = CASE
	                             WHEN @database_name LIKE '%RAW%' THEN 'RAW'
				     WHEN @database_name LIKE '%CLEAN%' THEN 'CLEAN'
				     END,
       @environment                = CASE
			             WHEN @database_name LIKE 'new_Raw_Dev' THEN 'new_Raw_Dev'
				     WHEN @database_name LIKE 'new_Clean_Dev' THEN 'new_Raw_Dev'
				     WHEN @database_name LIKE 'new_Raw_Test' THEN 'new_Raw_Test'
				     WHEN @database_name LIKE 'new_Clean_Test' THEN 'new_Raw_Test'
				     WHEN @database_name LIKE 'new_Raw_UAT' THEN 'new_Raw_UAT'
				     WHEN @database_name LIKE 'new_Clean_UAT' THEN 'new_Raw_UAT'
				     WHEN @database_name LIKE 'new_RAW' THEN 'new_Raw'
				     WHEN @database_name LIKE 'new_Clean' THEN 'new_Raw'
                                     END
-- log start
EXEC log_message
        @p_log_header_key         = @log_header_key OUTPUT
      , @p_processor_name_text    = @procedure_name
      , @p_message_template_code  = 'PROCESS_START'
      , @p_token_a                = @procedure_name
	  

So my question is how to execute the Log_message proc against respective database using SP_ExecuteSQL based on  @environment parameter in the proc.

Any help is greatly appreciated..

Many Thanks

Bhanu

              

• Edited by bhanu_nz 6 hours 12 minutes ago

I want to make sure i understand correctly what you are trying to do here. the @environment variable is going to be used to set the database context? and once the database context is switched you want to run log_message sp against that database?

Can you also post the ddl for log_message sp? you may be able to actually use sp_executesql within log_message stored proc to accomplish what you are looking to do

Thanks

The trick I learned from Erland is to prefix the sp_executeSQL with the name of the DB, e.g.

declare @sqlCommand = @environment+'.sp_executeSQL'

execute @sqlCommand, other parameters for sp_executeSQL.

i was just going to suggest modifying the log_message sp to use three part names  for dml commands and have @env variable passed in to be used within log_message sp dynamic sql code. That way all major code modifications are only done to log_message sp and all other sp just updated to use new parameters for the call...

But your solution works as well :)

Hi SQL Guru,

Yes - @environment variable is going to be used to set the database context and I want @environment variable
is going to be used to set the database context

below is the ddl for log_message sp

CREATE Proc [process].[log_message] 
  (
    @p_log_header_key         int           OUTPUT
  , @p_processor_name_text    varchar(255)    
  , @p_message_template_code  varchar(50)
  , @p_token_a                varchar(1000) = ''  
  , @p_token_b                varchar(1000) = '' 
  , @p_token_c                varchar(1000) = '' 
  , @p_token_d                varchar(1000) = '' 
  , @p_token_e                varchar(1000) = '' 
  )
  AS


SET NOCOUNT ON


DECLARE @raw_db_name     varchar(1000),
        @proc_name       varchar(2000),
        @sql_text        nvarchar(max)


SELECT @raw_db_name = metadata.fn_get_raw_db_name()

SELECT @proc_name = @raw_db_name + '.process.log_message'

SELECT @sql_text = 'exec @proc_name @p_log_header_key   OUTPUT  
                                    ,@p_processor_name_text   
                                    ,@p_message_template_code 
                                    ,@p_token_a
                                    ,@p_token_b
                                    ,@p_token_c
                                    ,@p_token_d
                                    ,@p_token_e'                                     

EXEC sp_executesql @sql_text,
                       N' @proc_name                varchar(2000)
                         ,@p_log_header_key         int     OUTPUT      
                         ,@p_processor_name_text    varchar(255)
                         ,@p_message_template_code  varchar(50)
                         ,@p_token_a                varchar(1000)
                         ,@p_token_b                varchar(1000)
                         ,@p_token_c                varchar(1000)
                         ,@p_token_d                varchar(1000)
                         ,@p_token_e                varchar(1000)',
                          @proc_name
                         ,@p_log_header_key         OUTPUT
                         ,@p_processor_name_text
                         ,@p_message_template_code
                         ,@p_token_a  
                         ,@p_token_b     
                         ,@p_token_c   
                         ,@p_token_d  
                         ,@p_token_e    

RETURN 0

GO

Thanks

Bhanu

I may be missing something, but wont you only  need to update the code line below:

SELECT @raw_db_name = metadata.fn_get_raw_db_name()

TO:

SELECT @raw_db_name =@environment

Where the @environment is passed in from parent sp?