sp_cursoropen - does not return rowcount
-
Hi all, I made one procedure in which i use sp_cursoropen procedure and it has one parameter for rowcount. When i execute the procedure, it does not return rowcount, i mean it returns -1 even there is 20 records in my table. Can anybody help me... Thanks
Krishnraj
-
Hi all, I made one procedure in which i use sp_cursoropen procedure and it has one parameter for rowcount. When i execute the procedure, it does not return rowcount, i mean it returns -1 even there is 20 records in my table. Can anybody help me... Thanks
Krishnraj
Hi Put return at the end of you're stored procedure declare @counter int select @counter = count(*) from mytable return @counter or set rowcount as output parameter and print it. or set nocount off greetz
-
Hi Put return at the end of you're stored procedure declare @counter int select @counter = count(*) from mytable return @counter or set rowcount as output parameter and print it. or set nocount off greetz
Hi topcatalpha, Thanks for reply, but sp_cursoropen has one parameter which returns the total count of table. so no need to fire extra query as u said. sp_cursoropen is sql server 2000's in built system procedure i think there may be some other settings. any way thanks buddy...
Krishnraj
-
Hi all, I made one procedure in which i use sp_cursoropen procedure and it has one parameter for rowcount. When i execute the procedure, it does not return rowcount, i mean it returns -1 even there is 20 records in my table. Can anybody help me... Thanks
Krishnraj
With what parameters are you callin this sp. It would depend on the cursor type if the rowcount can be returned. For example if the cursor is forward only, AFAIK the actual rowcount cannot be determined.
The need to optimize rises from a bad design. My articles[^]
-
With what parameters are you callin this sp. It would depend on the cursor type if the rowcount can be returned. For example if the cursor is forward only, AFAIK the actual rowcount cannot be determined.
The need to optimize rises from a bad design. My articles[^]
Hi Mika, Below is my query where u find variable @mainQuery. which is my table fetch query with Inner join with 3 tables.
Declare @cursor int, @rowcount int
Exec sp_cursoropen @cursor output,@mainQuery,16,8196,@rowcount output
Select @rowcount
Exec sp_cursorfetch @cursor,32,@RowNumber,@PageSize
Exec sp_cursorclose @cursor
Do u think that there may be a problem with inner join query? Thanks for replying...
Krishnraj
-
Hi Mika, Below is my query where u find variable @mainQuery. which is my table fetch query with Inner join with 3 tables.
Declare @cursor int, @rowcount int
Exec sp_cursoropen @cursor output,@mainQuery,16,8196,@rowcount output
Select @rowcount
Exec sp_cursorfetch @cursor,32,@RowNumber,@PageSize
Exec sp_cursorclose @cursor
Do u think that there may be a problem with inner join query? Thanks for replying...
Krishnraj
No I don't believe that the inner join is the problem. Now you're using fast forward-only cursor. What happens if you change to keyset-driven cursor:
Exec sp_cursoropen @cursor output, @mainQuery,
1,8196, @rowcount outputor dynamic cursor
Exec sp_cursoropen @cursor output, @mainQuery,
2,8196, @rowcount outputThe need to optimize rises from a bad design. My articles[^]
-
No I don't believe that the inner join is the problem. Now you're using fast forward-only cursor. What happens if you change to keyset-driven cursor:
Exec sp_cursoropen @cursor output, @mainQuery,
1,8196, @rowcount outputor dynamic cursor
Exec sp_cursoropen @cursor output, @mainQuery,
2,8196, @rowcount outputThe need to optimize rises from a bad design. My articles[^]
-
Hi Mika, Yes your answer is correct, inner join is not problem here. And as per u said, i used keyset-driven cursor then it gives me count.. :-D So its briliant suggesion... Thank you boss....:rose:
Krishnraj