How do loop through a dynamic SQL statement using a cursor?
The problem is that your dynamic query never produces a null. When the condition in the dynamic query's WHERE clause is met, the output parameter is assigned a Y. But when the condition is not met, nothing happens, and the @res parameter keeps the value assigned at the sp_executesql line – consequently, the @result variable keeps its previous value too.

In order to produce a null, you need to change the structure of the dynamic query. Instead of this:

SELECT @var = … WHERE …


make it this:

SET @var = (SELECT … WHERE …)


That way, when the condition is not met, the SELECT will return nothing, which will be interpreted as a scalar null and assigned to the @var accordingly. You can then turn your null into N or anything else as necessary. You can, of course, do that immediately in the dynamic query if that is the only way you want to treat those nulls:

SET @res = ISNULL((SELECT 'Y' WHERE …), 'N');