How do loop through a dynamic SQL statement using a cursor? I have the code below. When i run it i get the same value for all the rows fetched by the cursor. I want it to display the result fetched by the dynamic SQL and if the value returned is null i want the code to display 'N'. Please help.

ALTER PROCEDURE [dbo].[DIM_MASTER] AS


DECLARE

@V_DIM VARCHAR(80),
@V_STMT nVARCHAR(max),
@result varchar(10),
@res varchar(10),
@CURSOR_DIM cursor


set @CURSOR_DIM = CURSOR 
 FOR 
 SELECT FieldName FROM desc_user.dim_master

open @CURSOR_DIM

fetch next from @CURSOR_DIM into @V_DIM

WHILE @@FETCH_STATUS = 0
BEGIN

set @V_STMT = 
'select  @res = '+'''Y''' +' 
        where exists(select * from [desc_user].[ticket_master] 
        where @v_dim is not null)'  


 EXECUTE sp_executesql @V_STMT, N'@v_dim varchar(80),@res varchar(10)         OUTPUT',@v_dim = @v_dim,@res = @resULT OUTPUT
print @v_stmt


select @res as result

UPDATE desc_user.Dimension_Details set FieldValue = @res
where FieldName = @V_DIM


FETCH NEXT FROM @CURSOR_DIM
                INTO @V_DIM
                END

                CLOSE @CURSOR_DIM
                DEALLOCATE @CURSOR_DIM
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');