Using a variable in an IN clause in sql server 2017

That's not passing a variable to the IN() clause. It's straight-up injection-vulnerable string concatenation, so be very careful how you use it.

But the issue is here:

SET @sql = 'Select AllPosiable.UserID, AllPosiable.AgentSysID, 0' + 
            'From (' +

There's no whitespace in the string after the 0 at the end of the first line, no whitespace before the word From at the beginning of the second line, and the line break between them is not part of the resulting string. You end up with this:

Select AllPosiable.UserID, AllPosiable.AgentSysID, 0From (...

Try this:

SET @sql = 'Select AllPosiable.UserID, AllPosiable.AgentSysID, 0' + 
            ' From (' +

and make sure you're doing something similar at every line break.

Additionally, you need to look at this excerpt:

'AND AgentName NOT LIKE %Texas State Low Cost%' +

The expression %Texas State Low Cost% will not be part of a string literal in the resulting SQL code. You need to do this:

 'AND AgentName NOT LIKE ''%Texas State Low Cost%''' +

and, again, make sure all of the string literals in the SQL statement get similar treatment.

You have multiple problems in your query. Try this:

SET @sql = N'  -- strings should nvarchar for sp_executesql
Select AllPosiable.UserID, AllPosiable.AgentSysID, 0
From (Select AgentSysID, UserID
      From (Select AGT_Agent.AgentSysID
            From AGT_Agent
            Where OwnerSysID IN (@OwnerSysID) and
                  AgentName NOT LIKE ''%Texas State Low Cost%'' and
                  AgentName NOT LIKE ''%TSLC%''
           ) AgentIDs Full Join
           (Select DISTINCT IW_UserAgentRelationship.UserID
            From AGT_Agent Left Join
                 IW_UserAgentRelationship
                 On IW_UserAgentRelationship.AgentID = AGT_Agent.AgentSysID
            Where OwnerSysID IN (@OwnerSysID) and
                  IW_UserAgentRelationship.isDefault = 1 and
                  AgentName NOT LIKE ''%Texas State Low Cost%'' and
                  AgentName NOT LIKE ''%TSLC%''
           ) UserIDs
           On 1 = 1
      ) AllPosiable Left Join
      IW_UserAgentRelationship
      On IW_UserAgentRelationship.AgentID = AllPosiable.AgentSysID and
         IW_UserAgentRelationship.UserID = AllPosiable.UserID
Where IW_UserAgentRelationship.AgentID Is NULL
' ;

SET @sql = replace(@sql, n'@OwnerSysID', @OwnerSysID)

EXEC sp_executesql @sql;

Your basic problem is not understanding that a string can span multiple lines in SQL Server. That means that you don't have to concatenate a bunch of different lines together. In your case, you are merging together keywords, so the SQL is not understandable. If you had printed out @sql, this would be obvious.

Another problem is that the like patterns are not surrounded by single quotes. Within a string, these need to be duplicated.

I didn't look at the logic of the query itself, but I really doubt that full join is necessary.

I'm guessing you're taking the dynamic sql route just because you need to pass @OwnerSysID as a comma separated values to put it inside the IN()

if that is the case, then use TVF to pass a variable with a comma separated values without using the dynamic sql.

I've mentioned this method in this post

Basically, what you need is a Table Valued function that will handle @OwnerSysID to convert its values to rows (with the help of XML technique), then use it inside IN()

Needed Function :

CREATE FUNCTION Split(@String VARCHAR(MAX))
RETURNS TABLE 
AS
RETURN 
(
    SELECT LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) MyString
    FROM (
        SELECT CAST('<XMLRoot><RowData>' + REPLACE(@String,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) MyString
    ) D
    CROSS APPLY MyString.nodes('/XMLRoot/RowData')m(n)
)

then you can use it in your query like this :

OwnerSysID IN (SELECT * FROM dbo.Split(@OwnerSysID))

With this method, you don't need the dynamic sql, just a normal query will be enough.