CREATE PROCEDURE [dbo].[spDeleteSettings]
	@id int
AS
BEGIN
	DECLARE @error varchar(50)
	SET @error =
		CASE
			WHEN EXISTS ( SELECT 1 FROM tblSettings WHERE parent_id = id AND id = @id )
			THEN 'A root node cannot be deleted'
			ELSE ''
		END
	IF len(@error) > 0 BEGIN
		RAISERROR(@error, 16, 1)
	END ELSE BEGIN
		WITH cteSettings(id) AS
		(
			SELECT id
			FROM tblSettings 
			WHERE id = @id
			UNION ALL
			SELECT t2.id
			FROM cteSettings t1
			JOIN tblSettings t2
			ON t2.parent_id = t1.id
		)
		DELETE t2
		FROM cteSettings t1
		JOIN tblSettings t2
		ON t2.id = t1.id
	END
END