passing multiple datatypes into dynamic sql-values not passing in parameterized query

I have a dynamic SQL query inside a stored procedure that works and gives me the correct results. But it is taking too long-because I have to compare as varchar instead of int. I believe @query variable in SQL server requires the statement to be a unicode. Here is the dynamic sql part

ALTER PROCEDURE [dbo].[sp_GetRows]( @Id varchar(64))
AS
BEGIN
 DECLARE @Query nvarchar(4000),
 @Comp varchar(256)

SELECT @Comp
=  STUFF(( 
        SELECT DISTINCT ',' + char(39)+
        tci.Component +char(39)
        FROM  TCI tci WITH(NOLOCK) 
            JOIN CDetail cd WITH(NOLOCK) 
           ON  tci.ParentCId = cd.CIdentifier 
            WHERE tci.ParentCId  = @Id
            AND cd.ParentBranch IS NULL
            FOR XML PATH('')),1,1,'')
    SET @Query
    = 'WITH CTE AS 
        (       
            SELECT '+@Id+' as ParentCId, CIdentifier as ChildCId,
            a.Comp as Comp 
            from dbo.CD cd WITH(NOLOCK)             

               INNER JOIN  

            (SELECT DISTINCT ChildCId,Comp 
            FROM TCI tc WITH(NOLOCK) 
            WHERE ParentCId = '+ @Id + '

                                ) a 
            ON cd.CIdentifier= a.ChildCId
        );
       EXEC (@Query)
        END;

Here is the comparison- SELECT CIdentifier FROM #tempTable temp WITH(NOLOCK) WHERE temp.CIdentifier < '+@Id+'....

This compares as CIdentifier =1122233 instead of CIdentifier ='1122233' because dynamic SQL is not allowing me to pass it as an int. I keep getting the 'cannot convert varchar to int error'

So I used parameterized query - hoping that would enable me to pass int values.Here is the query part

        SET @Query
    = N';WITH CTE AS 
        (       
            ......
             (SELECT DISTINCT ChildCId,Comp 
                    FROM TCI tc WITH(NOLOCK) 
                    WHERE ParentCId = @Id
                    AND ChildCId + tc.Comp
                        NOT IN
            (SELECT ChildId + Comp FROM dbo.TCI WITH(NOLOCK) 
        WHERE ParentId IN   (SELECT CIdentifier FROM  #tempTable WITH(NOLOCK) 
                            WHERE  temp.CIdentifier < @Idn
                            AND Comp IN ( @Comp))                   
                            )
                            ) 
            )a 
         ON cd.CIdentifier= a.ChildId
        )
        SELECT * FROM CTE;'


EXEC sp_executeSQL @Query,'@Id VARCHAR(64),@Idn INT,@comp VARCHAR(256)',@Id=@Id,@Idn=@Idn,@comp =@comp 

This gives me incorrect results and when I saw the execution using a trace - saw that values are not being passed onto the query. How can I get the query to pick up the variables?

Just change WHERE ParentCId = '+ @Id + ' to WHERE ParentCId = '+ cast(@Id as varchar(16)) + ' in the first query. The problem is SQL Server see's + as addition when the value is a numeric type, or date, and concatenation when it isn't. This is where you get the error from. However, when you do this, it will not make SQL Server compare it as a string literal so you don't have to worry about that. You can see this if you use PRINT (@Query) at the end instead of EXEC (@Query)

Note, this needs to be changed at the other locations you have any NUMERIC data type, like in the SELECT portion, SELECT '+ cast(@Id as varchar(16)) +'

Also, you code doesn't show where @Id value comes from, so be cautious of SQL injection here.