title: Scope of variables in SQL Server procedure when using cursors
body: <p>I am trying to store the data retrieved from DB in to one variable and return it to java code.</p>
  
  <p>It is returning NULL value. This is because of the scope of variables in the cursor. Can anybody suggest a solution for it?</p>
  
  <p>Here is my procedure:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[rml_ups_profilerscheduler] @RuleIds varchar(200) output
      -- Add the parameters for the stored procedure here
      --&lt;@Param1, sysname, @p1&gt; &lt;Datatype_For_Param1, , int&gt; = &lt;Default_Value_For_Param1, , 0&gt;, 
      --&lt;@Param2, sysname, @p2&gt; &lt;Datatype_For_Param2, , int&gt; = &lt;Default_Value_For_Param2, , 0&gt;
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
      DECLARE @rulescheduleid varchar(50), @scheduletype varchar(50),@finalop varchar(50)
      DECLARE cursorName CURSOR GLOBAL
      FOR
      select distinct rulescheduleid,scheduletype
      from rml_ups_ruleschedulemaster 
      OPEN cursorName -- open the cursor
      FETCH NEXT FROM cursorName
      INTO @rulescheduleid, @scheduletype
       set @finalop=''
       if(lower(@scheduletype) ='daily')
       set @finalop=@finalop+','+(SELECT CONVERT(varchar(150),
      (select ruleid from rml_ups_ruleschedulemaster  a,rml_ups_rulescheduletimemapping b
       where a.rulescheduleid=b.rulescheduleid and b.schedulestarthour &lt;= (SELECT DATEPART(hh, GETDATE()))
       and scheduleendhour &gt;=(SELECT DATEPART(hh, GETDATE()))
       and a.rulescheduleid=1)))
  
      -- PRINT @rulescheduleid + ' ' + @scheduletype -- print the name
       if(lower(@scheduletype) ='weekly')
  
       set @finalop=@finalop+','+(SELECT CONVERT(varchar(150),
      (select ruleid from rml_ups_ruleschedulemaster  a,rml_ups_rulescheduletimemapping b
       where a.rulescheduleid=b.rulescheduleid and b.schedulestarthour &lt;= (SELECT DATEPART(hh, GETDATE()))
       and scheduleendhour &gt;=(SELECT DATEPART(hh, GETDATE())) and a.scheduledayofweek =(SELECT DATEPART(dw, GETDATE())) 
       and a.rulescheduleid=@rulescheduleid)))
  
       if(lower(@scheduletype) ='monthly')
  
        set @finalop=@finalop+','+(SELECT CONVERT(varchar(150),
      (select ruleid from rml_ups_ruleschedulemaster  a,rml_ups_rulescheduletimemapping b
       where a.rulescheduleid=b.rulescheduleid and b.schedulestarthour &lt;= (SELECT DATEPART(hh, GETDATE()))
       and scheduleendhour &gt;=(SELECT DATEPART(hh, GETDATE())) and a.scheduledayofmonth =(SELECT DATEPART(dd, GETDATE())) 
       and a.rulescheduleid=@rulescheduleid)))
  
       if(lower(@scheduletype) ='yearly')
  
        set @finalop=@finalop+','+(SELECT CONVERT(varchar(150),
      (select ruleid from rml_ups_ruleschedulemaster  a,rml_ups_rulescheduletimemapping b
       where a.rulescheduleid=b.rulescheduleid and b.schedulestarthour &lt;= (SELECT DATEPART(hh, GETDATE()))
       and scheduleendhour &gt;=(SELECT DATEPART(hh, GETDATE())) and a.scheduledayofmonth =(SELECT DATEPART(dd, GETDATE()))  and 
       a.schedulemonth=(SELECT DATEPART(mm, GETDATE())) 
       and a.rulescheduleid=@rulescheduleid)))
  
       if(lower(@scheduletype) ='on at time')
  
       set @finalop=@finalop+','+(SELECT CONVERT(varchar(150),
      (select ruleid from rml_ups_ruleschedulemaster  a,rml_ups_rulescheduletimemapping b
      where a.rulescheduleid=b.rulescheduleid and b.schedulestarthour &lt;= (SELECT DATEPART(hh, GETDATE()))
      and scheduleendhour &gt;=(SELECT DATEPART(hh, GETDATE())) and a.scheduledayofmonth =(SELECT DATEPART(dd, GETDATE()))
      and schedulemonth=(SELECT DATEPART(mm, GETDATE()))  and 
      a.scheduleyear=(SELECT DATEPART(yy, GETDATE())) 
      and a.rulescheduleid=@rulescheduleid)))
  
      --PRINT @rulescheduleid + ' ' + @scheduletype -- print the name
      --PRINT @@FETCH_STATUS
      WHILE @@FETCH_STATUS = 0
      BEGIN
      ---PRINT @@FETCH_STATUS
  
       FETCH NEXT FROM cursorName
       INTO @rulescheduleid, @scheduletype
  
       if(lower(@scheduletype) ='daily')
  
       set @finalop=@finalop+','+(SELECT CONVERT(varchar(150),
      (select ruleid from rml_ups_ruleschedulemaster  a,rml_ups_rulescheduletimemapping b
       where a.rulescheduleid=b.rulescheduleid and b.schedulestarthour &lt;= (SELECT DATEPART(hh, GETDATE()))
       and scheduleendhour &gt;=(SELECT DATEPART(hh, GETDATE()))
       and a.rulescheduleid=@rulescheduleid)))
  
      -- PRINT @rulescheduleid + ' ' + @scheduletype -- print the name
       if(lower(@scheduletype) ='weekly')
  
       set @finalop=@finalop+','+(SELECT CONVERT(varchar(150),
      (select ruleid from rml_ups_ruleschedulemaster  a,rml_ups_rulescheduletimemapping b
       where a.rulescheduleid=b.rulescheduleid and b.schedulestarthour &lt;= (SELECT DATEPART(hh, GETDATE()))
       and scheduleendhour &gt;=(SELECT DATEPART(hh, GETDATE())) and a.scheduledayofweek =(SELECT DATEPART(dw, GETDATE())) 
       and a.rulescheduleid=@rulescheduleid)))
  
       if(lower(@scheduletype) ='monthly')
  
       set @finalop=@finalop+','+(SELECT CONVERT(varchar(150),
      (select ruleid from rml_ups_ruleschedulemaster  a,rml_ups_rulescheduletimemapping b
       where a.rulescheduleid=b.rulescheduleid and b.schedulestarthour &lt;= (SELECT DATEPART(hh, GETDATE()))
       and scheduleendhour &gt;=(SELECT DATEPART(hh, GETDATE())) and a.scheduledayofmonth =(SELECT DATEPART(dd, GETDATE())) 
       and a.rulescheduleid=@rulescheduleid)))
  
       if(lower(@scheduletype) ='yearly')
  
       set @finalop=@finalop+','+(SELECT CONVERT(varchar(150),
      (select ruleid from rml_ups_ruleschedulemaster  a,rml_ups_rulescheduletimemapping b
       where a.rulescheduleid=b.rulescheduleid and b.schedulestarthour &lt;= (SELECT DATEPART(hh, GETDATE()))
       and scheduleendhour &gt;=(SELECT DATEPART(hh, GETDATE())) and a.scheduledayofmonth =(SELECT DATEPART(dd, GETDATE()))  and 
       a.schedulemonth=(SELECT DATEPART(mm, GETDATE())) 
       and a.rulescheduleid=@rulescheduleid)))
  
       if(lower(@scheduletype) ='on at time')
  
       set @finalop=@finalop+','+(SELECT CONVERT(varchar(150),
      (select ruleid from rml_ups_ruleschedulemaster  a,rml_ups_rulescheduletimemapping b
      where a.rulescheduleid=b.rulescheduleid and b.schedulestarthour &lt;= (SELECT DATEPART(hh, GETDATE()))
      and scheduleendhour &gt;=(SELECT DATEPART(hh, GETDATE())) and a.scheduledayofmonth =(SELECT DATEPART(dd, GETDATE()))
      and schedulemonth=(SELECT DATEPART(mm, GETDATE()))  and 
      a.scheduleyear=(SELECT DATEPART(yy, GETDATE())) 
      and a.rulescheduleid=@rulescheduleid)))
      PRINT @finalop 
      set @RuleIds=@finalop
      PRINT  @RuleIds 
      -- print the name
  
      END
      --set @RuleIds=@finalop
      --PRINT @RuleIds 
      CLOSE cursorName
      -- close the cursor
      --PRINT  @RuleIds  
      DEALLOCATE cursorName
      -- Deallocate the cursor
  
  
  END
  </code></pre>