File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases 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 and Relational Databases
Bookmark "Stored Procedure Issue" Watch "Stored Procedure Issue" New topic

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.

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

SCJP Tipline, etc.
I agree. Here's the link:
subject: Stored Procedure Issue
It's not a secret anymore!