Problem when invoking stored procedure with cursor from inside .net

[ookdeh]

Dear all,

i'm facing a problem with my storedprocedure which happened when i ran my web application and reach to the point where my class invoke this storedprocedure,

my SP contains a cursor that built his sql according to certain condition, so i put the "SET @cur Cursor For....." inside the if block (definitely i've declared it under AS keyword directly) and this SP is working well inside sql server(I've tested it), BUT when my ASP.net code invoke this SP it gives me the following error :
"The Variable @cur does not currently have a cursor allocated to it" repeated as much as there are IF clauses in my SP,


Please Help.

Regards,

[Plater]

it's of type "curser"? I don't think I've ever heard of that data type.
Is it used to "print output" or something?
Like a Stream would be used in .Net?

[ookdeh]

Cursor is a datatype used to store result for certain sql and then you can went through this result inside stored procedure ...

[Plater]

Hmm I guess I can only suggest you check "@cur is NOT null" first.
Maybe something else is going wrong beforehand and the @cur is not getting any results to be scanned?

[ookdeh]

Hello ,

Actually i think the main issue that asp.net can't see the @Cur variable when it is assigned inside an inner scope (the if statment scope) because this error message usually comes when the SP didn't see the "SET @Cur For " Statment and i didn't know why this comes from asp.net side although it is well from SP side.

[Plater]

How is your stored procedure created? I believe only parameters defined as being arguments to the stored procedure can be seen from the "outside"

[ookdeh]

Hello,

I've created the SP manually and not programaticly, And hereunder the SP ;

ALTER PROCEDURE SP_GetEngLogsIDPerStatus_Date_ForTrackEng_Page

(
@UID int ,
@Status nvarchar(10)='',
@isDR nvarchar(10)='',
@FD as datetime ='',
@TD as datetime ='',
@LogsIDs nvarchar(1000)='' output
)

AS
Set NoCount ON
Declare @MaxId int
Declare @EscId int
Declare @LogId int
declare @TempLIds nvarchar(1000)
set @tempLIds = ''
Declare @Cur cursor
Declare @sql nvarchar(300)



if @status = 'closed'

begin
print @status
if @isDR = 'True'
begin
print 'interval Req'
SET @Cur = cursor for
SELECT Escalation.LogCID, Escalation.EscID
FROM Escalation INNER JOIN
[Log-Call] ON Escalation.LogCID = [Log-Call].LogCID
WHERE (Escalation.UserID = @UID) AND ([Log-Call].LogCStatus = 'Closed')AND ([Log-Call].ClosedDate BETWEEN @FD AND DATEADD(d,1,@TD))

end

else if @isDR = 'False'
begin
SET @Cur = cursor for
SELECT Escalation.LogCID, Escalation.EscID
FROM Escalation INNER JOIN
[Log-Call] ON Escalation.LogCID = [Log-Call].LogCID
WHERE (Escalation.UserID = @UID) AND ([Log-Call].LogCStatus = 'Closed')

end
end

else if @status = 'open' or @status = 'pending'
begin
print @status
if @isDR = 'True'
begin
SET @Cur = cursor for
SELECT Escalation.LogCID, Escalation.EscID
FROM Escalation INNER JOIN
[Log-Call] ON Escalation.LogCID = [Log-Call].LogCID
WHERE (Escalation.UserID = @UID) AND ([Log-Call].LogCStatus = 'Open' OR
[Log-Call].LogCStatus = 'Pending') AND (Escalation.EscDate BETWEEN @FD AND DATEADD(d,1,@TD))


end

else if @isDR = 'False'
begin
SET @Cur = cursor for
SELECT Escalation.LogCID, Escalation.EscID
FROM Escalation INNER JOIN
[Log-Call] ON Escalation.LogCID = [Log-Call].LogCID
WHERE (Escalation.UserID = @UID) AND ([Log-Call].LogCStatus = 'Open' OR
[Log-Call].LogCStatus = 'Pending')

end
end






OPEN @cur
FETCH NEXT FROM @cur INTO @LogID,@EscID
WHILE @@FETCH_STATUS = 0
begin

select @MaxID=max(EscID) from Escalation where logcid = @logID

if @MaxID = @EscID
begin
SET @TempLIds = @TempLIds + ',' + LTrim(str(@logID))
End

FETCH NEXT FROM @cur INTO @LogID,@EscID
End

set @LogsIds = @TempLIds
print @LogsIDs


close @Cur
deallocate @cur
print 'Finaly -- ' + @TempLIds

If @@ERROR <> 0 GoTo ErrorHandler
Set NoCount OFF


ErrorHandler:
Return(@@ERROR)

[Plater]

These:

@UID int ,
@Status nvarchar(10)='',
@isDR nvarchar(10)='',
@FD as datetime ='',
@TD as datetime ='',
@LogsIDs nvarchar(1000)='' output
 

Along with:

RETURN_VALUE
 

Are the only variables accessable outside of the procedure (the calling code), @CUR is not one of them, so you should not be able to get at it from the .NET side

[ookdeh]

first thank you Plater for your help,

actually i'm not accessing @cur from .Net side, the only thing i retreive value is "@LogsIDs" variable from .Net side ,this variable is been populated inside @cur iteration, and this which made me confused when .Net through the error message regarding the Cursor and i didn't called it anyware. i belive this error comes from SQL to .Net .

[ookdeh]

Hello,

actually i've created 4 seperated stored procedures to solve this issue and i have differntiate between thier calling from .net side and everything is OK.

Regards.