File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Stored Procedure Issue Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Stored Procedure Issue" Watch "Stored Procedure Issue" New topic
Author

Stored Procedure Issue

Corey McGlone
Ranch Hand

Joined: Dec 20, 2001
Posts: 3271
So I have what could be considered a very long question, but I'll try to trim it down to a reasonable size. Basically, I was originally using a stored procedure (on MS SQL Server) which utilized an OUTPUT parameter - something like this:



This worked and I was able to get my value through that output parameter. Then of course, a new requirement came along. The customer wants to be able to run this query for multiple areas at once, but we don't know how many areas that might be; it might be one, or two, or six, or twenty areas.

Unfortunately, I don't know a good way to send an array to a stored procedure, so I tried something like this:



This, unfortunately, died a horrible death. I need the EXEC statement in order to send a list of numbers into the stored proc and use them within the "IN" clause. However, it doesn't seem as if I can use variables, such as @numberOfMatchingRecords and @startDate within the EXEC statement.

I was able to fix the startDate and endDate issues like this:



Unfortunately, there's nothing I can do about the variable @numberOfMatchingRecords - I want that variable name in there so that I can assign the result value to it. So this approach just doesn't seem to work. Assigning the COUNT(*) to a variable dictates that I can't have an EXEC statement while the introduction of the IN clause dictates that I must have an EXEC statement.

So I came up with a couple alternatives. While I'm moderately proud of the creativity used to come up with these, I'm mortally ashamed of the resultant code.

Solution #1: Use the MS SQL Server Command @@ROWCOUNT

I was able to use the database specific command @@ROWCOUNT to get the variable assigned after doing the query, like this (note that I took out the COUNT operator):



This seems to work and isn't even that disgusting looking, but it depends upon a SQL Server specific command and, in addition, my stored procedure now returns a result set, which it didn't used to do and I have no use for.

Solution #2: Use a Temp Table

The second solution I came up with was to insert the data into a temp table so that I didn't have to set the value to a variable right away, like this (Notice the reintroduction of the COUNT operator):



This also seems to work, although I'm a little surprised that the SELECT I'm using to assign the variable actually works - I was expecting a failure, there.

The benefits of this approach include that I'm not tied directly to a database command (although the fact that I'm using stored procs, at all, may contradict that benefit) and my stored proc is not returning a result set, which I don't want, anyway.



So I know I said I was going to try to keep that short and, in reality you have no idea how much I left out. There's a lot of other garbage going on behind the scenes that led me to be using a stored proc and output parameters in the first place, but I'd rather not get into all of that, if I can help it.

What I really want to know is if anyone can see any glaring flaws with these potential solutions (aside from the ugliness of them) or if anyone has a better solution that I might use.

Thanks,
Corey
[ December 11, 2007: Message edited by: Corey McGlone ]

SCJP Tipline, etc.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Stored Procedure Issue
 
Similar Threads
Java and Sybase nested stored procedures
Java and Sybase nested stored procedures
executeQuery( ) method
inserting values in database after calculation
JDBC Stored Procs and Temp tables