Stored Procedure Issue

[Corey McGlone]

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:

DECLARE @startDate datetime,
DECLARE @endDate datetime,
DECLARE @numberOfMatchingRecords int OUTPUT
 
SELECT @numberOfMatchingRecords = COUNT(*)
FROM MyTable
WHERE eventDt >= @startDate
AND eventDt <= @endDate

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:

DECLARE @startDate datetime,
DECLARE @endDate datetime,
DECLARE @areas varchar(50),  -- This would be a list of areas ids, such as '1, 4, 12'
DECLARE @numberOfMatchingRecords int OUTPUT
 
EXEC('
SELECT @numberOfMatchingRecords = COUNT(*)
FROM MyTable
WHERE eventDt >= @startDate
AND eventDt <= @endDate
AND eventArea IN (' + @areas + ')
')

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:

EXEC('
SELECT @numberOfMatchingRecords = COUNT(*)
FROM MyTable
WHERE eventDt >= \'' + @startDate + \''
AND eventDt <= \'' + @endDate + \''
AND eventArea IN (' + @areas + ')
')

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):

EXEC('
SELECT eventID
FROM MyTable
WHERE eventDt >= \'' + @startDate + \''
AND eventDt <= \'' + @endDate + \''
AND eventArea IN (' + @areas + ')
 
SET @numberOfMatchingRecords = @@ROWCOUNT

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):

CREATE TABLE #TEMP
(
    matchingRecords int
);
 
EXEC('
INSERT INTO #TEMP
SELECT COUNT(*)
FROM MyTable
WHERE eventDt >= \'' + @startDate + \''
AND eventDt <= \'' + @endDate + \''
AND eventArea IN (' + @areas + ')
 
SELECT @numberOfMatchingRecords = matchingRecords FROM #TEMP
 
DROP TABLE #TEMP

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 ]