CREATE TABLE dbo.parent(some_id TINYINT IDENTITY(1, 1), string_val VARCHAR(10), CONSTRAINT pk PRIMARY KEY(some_id))
CREATE TABLE dbo.child(some_blah SMALLINT, some_id_from_parent TINYINT NOT NULL, CONSTRAINT f FOREIGN KEY (some_id_from_parent) REFERENCES dbo.parent(some_id)

CREATE PROC dbo.insert_into_child(@list_of_string_vals)
	
	-- @list of string_vals converted to a table var
	BEGIN TRY
		BEGIN TRAN
			INSERT INTO dbo.child(some_id_from_parent)
				SELECT P.some_id
					FROM dbo.parent P
						LEFT JOIN @list_of_string_vals C ON P.string_val = C.string_val -- left join, so if there are nulls then insert will fail cos of column definition
		COMMIT TRAN
	END TRY
	BEGIN CATCH
		-- roll back
	END CATCH

CREATE PROC dbo.insert_into_child(@list_of_string_vals varchar(8000))
AS
	BEGIN TRY
		BEGIN TRAN
			if exists
			(
				SELECT P.some_id
				FROM dbo.DelimitedSplit8K(@list_of_string_vals, ',') C
				LEFT JOIN dbo.parent P ON P.string_val = C.Item
				where p.string_val is null
			)
			RAISERROR('Not all values are in parent table.', 11, 1) --Must have a severity of 11 or higher and less than 20 so we get to the catch block
			
			INSERT INTO dbo.child(some_id_from_parent)
			SELECT P.some_id
			FROM dbo.DelimitedSplit8K(@list_of_string_vals, ',') C
			LEFT JOIN dbo.parent P ON P.string_val = C.Item
		COMMIT TRAN
	END TRY
	BEGIN CATCH
		select 'Do something here'
		rollback transaction
	END CATCH

create PROC [dbo].[insert_into_child](@list_of_string_vals varchar(8000))
AS
	BEGIN TRY
		BEGIN TRAN
			INSERT INTO dbo.child(some_id_from_parent)
			SELECT P.some_id
			FROM dbo.DelimitedSplit8K(@list_of_string_vals, ',') C
			LEFT JOIN dbo.parent P ON P.string_val = C.Item
		COMMIT TRAN
	END TRY
	BEGIN CATCH
		select 'inside catch'
		rollback transaction		
	END CATCH
GO