USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [internal].[usp_SEL_message]    Script Date: Sat 05-09-2026 8:34:59 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Silkwood Software
-- Create date: 08-06-2023
-- Description:	Initial creation.
-- Fetches error messages according to the selected language.
-- Input is the message id.  Output is the message text.
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2023
*/

-- =============================================
CREATE PROCEDURE [internal].[usp_SEL_message] 

	@message_id nvarchar(20),
	@message_text nvarchar(200) OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE
	    @connectedusersid varbinary(100)   = SUSER_SID(ORIGINAL_LOGIN()),   -- The SID of the connected user
		@sid_id bigint                     = NULL;

	-- Note that the selected row from the global_setting_group table is hard coded here
	BEGIN TRY
     SELECT @message_text = 
	        (SELECT message_text 
	           FROM messaging.message_details 
         INNER JOIN base.global_settings_groups 
                 ON messaging.message_details.language_id 
		 	        = base.global_settings_groups.language_id 
              WHERE message_id 
			        = @message_id 
				    AND base.global_settings_groups.setting_group_name = 'Master') 

--  ============================================================================================

				   -- Write to authorisation fail log
	IF @message_id = 'NoPermission'
	  BEGIN

	  
  		SELECT @sid_id = sid_id
          FROM user_restr.sid_list
         WHERE sid = @connectedusersid;

	  				  INSERT INTO user_restr.authorisation_fail_log
								  (sid_id, privilege_type, privilege_id, privilege_name, stored_procedure, failure_type)
						   VALUES (
									@sid_id,
									'Role',
									NULL,
									NULL,
									'[internal].[usp_SEL_message] Message = NoPermission',
									'Role checks failed to match at least one role'
									)
	  END						
--  ============================================================================================

    END TRY
	BEGIN CATCH
	   SET @message_text = 'A database message error occurred in [internal].[usp_SEL_message]';
	END CATCH
END
GO