将多个数据类型传递到动态sql值中而不传递参数化查询

我在存储过程中有一个动态SQL查询，该查询可以工作并提供正确的结果。但这花了太长时间，因为我必须将其比较为varchar而不是int。我相信SQL Server中的@query变量要求该语句为unicode。这是动态SQL部分

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;

这是比较-SELECT CIdentifier from #tempTable temp WITH（NOLOCK）WHERE temp.CIdentifier <'+ @ Id +'...。

相比之下，这是CIdentifier = 1122233而不是CIdentifier ='1122233'，因为动态SQL不允许我将其作为int传递。我不断收到“无法将varchar转换为int错误”

所以我使用了参数化查询-希望这可以让我传递int值。这是查询部分

        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 

这给了我错误的结果，并且当我看到使用跟踪的执行时-看到没有将值传递到查询中。如何获取查询以获取变量？