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 Thursday, July 30, 2015 12:55 AM

SELECT @log_message = @environment + '.dbo.log_message'
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

That is, EXEC accepts a variable for the procedure name.

Hi Erland,

Thanks a lot for your inputs, I think I am getting closer. But not sure why I am getting this error message while running the script below.

DECLARE
	
		@environment                   VARCHAR (500),
		@log_message                   VARCHAR(1000),
		@database_name                 Varchar(1000)
      
SELECT 
	   @log_message                = @environment + '.[Process].[log_message]',
	   @database_name              = 'new_Clean_Dev' ,
	   @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
exec @log_message    
                                   

Getting the error below

Any Thoughts?

Thanks

Bhanu

• Edited by bhanu_nz 10 hours 38 minutes ago

Instead of using one SELECT statement for your variables, set values for each variable separately and test with print commands first. Then you should be able to see the error.

BTW, why do you need the case statements? Looks like the change is only Clean -> Raw. In this case, just use Replace command, e.g.

set @environment = replace(@database, '_Clean', '_

@environment is null, appending other values to it still produces a null. Your EXEC has nothing to execute.

Hi Naomi,

Its my bad. I have got the order of variables(@log_message= @environment + '.[Process][log_message]',) wrong. You guys saved my day. I will also use Replace Command.

@Erland - You are a Legend.

Many Thanks,

Bhanu