You don't need any dynamic code. You can use CHARINDEX

INSERT INTO tmp_auditlog_results
SELECT S.docid, F.NEEDLE 
FROM tmp_auditlog_subselection AS S
CROSS JOIN tmp_auditlog_fields AS F
WHERE CHARINDEX(F.needle, S.haystack) != 0

Replace the select statement:

select * from tmp_auditlog_fields;

with following statement:

SELECT DISTINCT 'id' + CAST(A.id as VARCHAR(9999)) AS doc, needle,  B.haystack 
FROM tmp_auditlog_fields A
INNER JOIN tmp_auditlog_subselection B 
ON B.docid =  'id'+ CAST(A.id as VARCHAR(9999))

The answer from podiluska needed some renaming of column names. So i created this example starting from his query

Given these Tables

CREATE TABLE Haystacks (
  docid varchar(255),
  haystack varchar(255)
)
CREATE TABLE Needles (
  id int identity(1,1),
  fieldid varchar(255),
  needle varchar(255)
)

And these records

insert into Haystacks (docid, haystack) values
  ('id1', 'lorem ipsum dolor dog'),
  ('id2', 'the cat jumps over the lazy dog'),
  ('id3', 'i have no clue what to write');
insert into Needles (fieldid, needle) values
  ('a', 'dog'),
  ('b', 'dolor'),
  ('c', 'write');

This query

SELECT docid, needles.needle, haystack 
FROM Haystacks
INNER JOIN Needles ON Haystacks.haystack 
like '%'+Needles.needle+'%'

returns this result

See this demo fiddle this matches the expected query result

doc   needle  string
    ---------------------------------------------
    id1   dog     lorem ipsum dolor dog
    id2   dog     the cat jumps over the lazy dog
    id1   dolor   lorem ipsum dolor dog
    id3   write   i have no clue what to write

INSERT tmp_auditlog_results (doc,needle,string)
    SELECT doc, needles.needle, string 
    FROM  haystacks
    INNER JOIN needles ON haystacks.string like '%'+needles.needle+'%'