Scope of variables in SQL Server procedure when using cursors

Question

I am trying to store the data retrieved from DB in to one variable and return it to java code.

It is returning NULL value. This is because of the scope of variables in the cursor. Can anybody suggest a solution for it?

Here is my procedure:

ALTER PROCEDURE [dbo].[rml_ups_profilerscheduler] @RuleIds varchar(200) output
    -- Add the parameters for the stored procedure here
    --<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
    --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
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 <= (SELECT DATEPART(hh, GETDATE()))
     and scheduleendhour >=(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 <= (SELECT DATEPART(hh, GETDATE()))
     and scheduleendhour >=(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 <= (SELECT DATEPART(hh, GETDATE()))
     and scheduleendhour >=(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 <= (SELECT DATEPART(hh, GETDATE()))
     and scheduleendhour >=(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 <= (SELECT DATEPART(hh, GETDATE()))
    and scheduleendhour >=(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 <= (SELECT DATEPART(hh, GETDATE()))
     and scheduleendhour >=(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 <= (SELECT DATEPART(hh, GETDATE()))
     and scheduleendhour >=(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 <= (SELECT DATEPART(hh, GETDATE()))
     and scheduleendhour >=(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 <= (SELECT DATEPART(hh, GETDATE()))
     and scheduleendhour >=(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 <= (SELECT DATEPART(hh, GETDATE()))
    and scheduleendhour >=(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

Answer 1

Personally, I would not use a cursor at all. I would use CASE statements in a select to get the fields you want to send back to the app and return the whole data set. Then I would loop thorugh the data set onthe application side to set the parameter values as needed by the app.

Answer 2

i had modified my code procedure is running without any error but at the end of the loop values(retrived from db based on conditoins) asaigned to a variable is becoming empty after the loop is completed here is my modified code 

set @RuleIds='' DECLARE @RuleIds DECLARE cName CURSOR FOR select distinct id,stype from table1 OPEN cursorName -- open the cursor FETCH NEXT FROM cName INTO @rul, @sche

WHILE @@FETCH_STATUS = 0
BEGIN
 if(lower(@sched) ='daily')
 set @RuleIds=@RuleIds+','+(SELECT CONVERT(varchar(150),
(select ruleid from rml_ups_ruleschedulemaster  a,rml_ups_rulescheduletimemapping b
 where a.rulescheduleid=b.rulescheduleid and b.schedulestarthour <= (SELECT DATEPART(hh, GETDATE()))
 and scheduleendhour >=(SELECT DATEPART(hh, GETDATE()))
 and a.rulescheduleid=@rulescheduleid)))
-- here iam trying to store retreved values in to variable and returning to the java code it is retuning null values
PRINT @RuleIds 
-- it prints the values assaigned to @RuleIds variable ex values(13,12,2)
 FETCH NEXT FROM cName
 INTO @rul, @sche
END
CLOSE cName
DEALLOCATE cName
--after deaalocating of cursor the valuse stored in  @RuleIds(variable) is becoming empty
PRINT @RuleIds 
---this print statement printing  nothing 

Answer 3

Basically, your cursor is organised like this:

open cursor

fetch first row into vars

process the fetched values

while @@fetch_status = 0

  fetch next row into vars
  process the fetched values

end

close cursor

Now, the process the fetched values part is considerable in your procedure, and it is repeated twice in your code, which is one thing. One other, more important thing is, when you are processing the fetched values in your loop body, you are essentially doing it immediately after the values are fetched. But if it is the last iteration, it will fetch NULLs and, as a result of your processing the NULLs, your output variable ends up NULL as well.

So, what should you do in this situation? Just drop the process the fetched values stage before the loop and move the other one, which is after the fetch next row into vars step, to the position before it, so that the structure of your cursor becomes like this:

open cursor

fetch first row into vars

while @@fetch_status = 0

  process the fetched values
  fetch next row into vars

end

This way, you have only one place where you are processing the values and you are only doing it when the values are not NULL, because as soon as another FETCH NEXT brings about the NULLs, you exit the loop, preserving the cumulated output.

Answer 4

This bit:

WHILE @@FETCH_STATUS = 0
BEGIN
---PRINT @@FETCH_STATUS

 FETCH NEXT FROM cursorName
 INTO @rulescheduleid, @scheduletype

Is definitely wrong - @@FETCH_STATUS is updated based on the result of the previous FETCH statement - but you've already used the result of that FETCH before you reach this loop, and then you immediately perform another FETCH and don't check it's success until after you've used it.

The usual form would be:

DECLARE <cursor>
OPEN <cursor>

FETCH NEXT FROM <cursor>
WHILE @@FETCH_STATUS = 0
BEGIN
    <process result from previous fetch>

    FETCH NEXT FROM <cursor>
END

CLOSE <cursor>
DEALLOCATE <cursor>

At a guess, whenever you're going past the end of the result set, it's setting the local variables (e.g @rulescheduleid) to NULL, and then you're performing concatenation with NULL producing NULL results.

That being said, I've not read through the rest of the wall of code to work out what you're attempting to do and whether a cursor is needed at all.