USE [Elyse_DB] GO /****** Object: StoredProcedure [controlling].[usp_INS_tag_tree_perm_flist] Script Date: Wed 21-10-2026 4:50:16 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Silkwood Software -- Create date: 23-09-2024 -- Description: Initial creation -- Creates a new tag group permissions record to grant permission for members of a function list to edit a tag tree -- Input and output parameters are as declared below. /* COPYRIGHT NOTICE This database schema and stored procedures are protected by copyright. Copyright. Silkwood Software Pty. Ltd. 2024 */ -- ============================================= CREATE PROCEDURE [controlling].[usp_INS_tag_tree_perm_flist] @tag_treeid bigint = NULL, @functionlistid bigint = NULL, @message nvarchar(1000) = '' OUTPUT, @transaction_status nvarchar(50) = NULL 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 @username nvarchar(150) = ORIGINAL_LOGIN(), -- The username @userauthentication_status nchar(10) = 'Fail', -- The outcome of the authentication check of the user @validationmessage nvarchar(200) = '', -- Communicates that the transaction failed due to data validation @tempuserauth_status nchar(10) = '', @tempmessage nvarchar(300) = '', @tempvalidationstatus nchar(10) = '', @transaction_ready nchar(10) = 'Ready', @data_validation_status nchar(10) = 'Pass'; -- Parameters which have been initialised at declaration but not explicitly set might be output as null to calling functions. SET @transaction_status = 'Transaction not attempted'; -- Connected user authentication -- Authenticate the connected user for the role EXEC [internal].[usp_AUTHENTICATE_user_role] @role_to_check = 'Controller', @user_authentication_result = @userauthentication_status OUTPUT; IF @userauthentication_status = 'Fail' BEGIN -- The user does not have permission for this action SET @transaction_ready = 'Fail'; EXEC internal.usp_SEL_message @message_id = 'NoPermission', @message_text = @tempmessage OUTPUT; IF (@tempmessage IS NOT NULL) SET @message = CONCAT_WS(' | ', @message, ISNULL(@username, ''), ' ', @tempmessage); ELSE SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NoPermission. '); END IF @userauthentication_status = 'Pass' -- Don't do anything if the user is not authorised. BEGIN IF @tag_treeid = 0 SET @tag_treeid = NULL; -- Check if a tag tree id was supplied. IF @tag_treeid IS NULL BEGIN -- A tag tree id has not been supplied. SET @data_validation_status = 'Fail'; SET @transaction_ready = 'Fail'; EXEC internal.usp_SEL_message @message_id = 'NoTagTree', @message_text = @tempmessage OUTPUT; IF (@tempmessage IS NOT NULL) SET @message = CONCAT_WS(' | ', @message, @tempmessage); ELSE SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NoTagTree.'); END ELSE BEGIN IF NOT EXISTS (SELECT tag_tree_id FROM tagging.tag_tree_names WHERE tag_tree_id = @tag_treeid) BEGIN -- tag group id does not exist SET @data_validation_status = 'Fail'; SET @transaction_ready = 'Fail'; EXEC internal.usp_SEL_message @message_id = 'TagTreeNotExist', @message_text = @tempmessage OUTPUT; IF (@tempmessage IS NOT NULL) SET @message = CONCAT_WS(' | ', @message, @tempmessage); ELSE SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on TagTreeNotExist.'); END END -- Check the function list id IF @functionlistid = 0 SET @functionlistid = NULL; IF @functionlistid IS NULL BEGIN EXEC internal.usp_SEL_message @message_id = 'NoFunctionListID', @message_text = @tempmessage OUTPUT; IF (@tempmessage IS NOT NULL) SET @message = CONCAT_WS(' | ', @message, @tempmessage); ELSE SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on NoFunctionListID'); SET @data_validation_status = 'Fail'; SET @transaction_ready = 'Fail'; END ELSE -- function list has been supplied. Check if it is valid BEGIN IF NOT EXISTS (SELECT fln.function_list_id FROM people.function_list_names AS fln WHERE fln.function_list_id = @functionlistid) BEGIN SET @data_validation_status = 'Fail'; SET @transaction_ready = 'Fail'; EXEC internal.usp_SEL_message @message_id = 'FunctListIDInvalid', @message_text = @tempmessage OUTPUT; IF (@tempmessage IS NOT NULL) SET @message = CONCAT_WS(' | ', @message, @tempmessage); ELSE SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on FunctListIDInvalid.'); END END -- Check if a duplicate record exists IF @data_validation_status = 'Pass' BEGIN IF EXISTS (SELECT tag_tree_id FROM user_restr.tag_tree_permissions_flist WHERE tag_tree_id = @tag_treeid AND function_list_id = @functionlistid) BEGIN SET @data_validation_status = 'Fail'; SET @transaction_ready = 'Fail'; EXEC internal.usp_SEL_message @message_id = 'RecordExists', @message_text = @tempmessage OUTPUT; IF (@tempmessage IS NOT NULL) SET @message = CONCAT_WS(' | ', @message, @tempmessage); ELSE SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on RecordExists.'); END END -- Output the data validation status failed message IF @data_validation_status = 'Fail' BEGIN EXEC internal.usp_SEL_message @message_id = 'FailedDataValidation', @message_text = @tempmessage OUTPUT; IF (@tempmessage IS NOT NULL) SET @message = CONCAT_WS(' | ', @message, @tempmessage); ELSE SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on FailedDataValidation.'); END -- End data validation END -- End user authentication = Pass -- Execute the insert query IF @transaction_ready = 'Ready' BEGIN BEGIN TRY INSERT INTO user_restr.tag_tree_permissions_flist ( tag_tree_id, function_list_id) VALUES (@tag_treeid, @functionlistid); EXEC internal.usp_SEL_message @message_id = 'Success', @message_text = @tempmessage OUTPUT; IF (@tempmessage IS NOT NULL) SET @message = CONCAT_WS(' | ', @message, @tempmessage); ELSE SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on Success.'); SET @transaction_status = 'Good'; END TRY BEGIN CATCH IF XACT_STATE() <> 0 ROLLBACK TRANSACTION; SET @transaction_status = 'Bad'; EXEC internal.usp_SEL_message @message_id = 'InsertError', @message_text = @tempmessage OUTPUT; IF (@tempmessage IS NOT NULL) SET @message = CONCAT(@message, @tempmessage, ' | ', CONVERT(nvarchar(10),ERROR_NUMBER()), ' | ', ERROR_MESSAGE()); ELSE SET @message = CONCAT_WS(' | ', @message, 'A database level message error occurred on InsertError.'); END CATCH END END GO