• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Stored Procedure Issue

 
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.

Thanks,
Corey
[ December 11, 2007: Message edited by: Corey McGlone ]
 
reply
    Bookmark Topic Watch Topic
  • New Topic