Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Retrieving data from cursors of stored procedures in MS SQL server

 
Rajeev Srikhar
Ranch Hand
Posts: 75
Chrome Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello all, Can you please suggest me how to write the code which contains cursors in stored procedures in Ms SQL Server  so that I can retrieve using Result set in java program.

my code upto now:


when i use EXEC dbo.SampleProcedure;  I'm getting the output "Command(s) completed successfully." , instead i want all the firstnames and last names. Where i'm doing wrong?
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What do you want your procedure to do?

At the moment it does the SELECT and then iterates over the results.

If you want the cursor returned then just do the SELECT (no CURSOR FOR).
 
Rajeev Srikhar
Ranch Hand
Posts: 75
Chrome Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the reply.
My StoredProcedure with cursor should return a Resultset, so that the same ResultSet I can retrieve in java program. Please tell me the steps in doing that
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Then get rid of the whole cursor thing.

T-SQL will automatically return the last SELECT cursor:



That's pretty much it, which is why I asked what you wanted the procedure to do, as you may as well just do a SELECT.
 
Rajeev Srikhar
Ranch Hand
Posts: 75
Chrome Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for replying, actually I got the ResultSet by using Stored Procedure like you mentioned. But I'm curious to use cursors in stored procedures and retrieve Result set data in java program.
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Once you open the cursor in the procedure it is not then available for returning to the calling JDBC code.
That's the issue you had.
Because you started processing the cursor in the code then SQL Server didn't return the cursor.

ETA:  Actually, it might be more related to you closing the cursor at the end.
Not sure.
You might want to test that.
 
Anton Golovin
Ranch Hand
Posts: 527
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rajeev Srikhar wrote:Thanks for replying, actually I got the ResultSet by using Stored Procedure like you mentioned. But I'm curious to use cursors in stored procedures and retrieve Result set data in java program.


Hi, Rajeev,

Should only use cursors when line by line data-processing on your result set is unavoidable for some reason. Have pity on your db server.

Read up a good recent book on MS SQL server, Amazon has plenty.

With best regards,

Anton.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic