• Post Reply Bookmark Topic Watch Topic
  • New Topic

Stored Procedure Issue

Corey McGlone
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!