Preventing result sets in a stored procedure

[LampknLn45]

I have a stored procedure which uses a cursor to loop through the contents of one table and delete records from a temp table when certain date conditions exist. My procedure is now returning two result sets: The first result set is that of the cursor. The second result set (the one that I want) is the result of my "cleaned" up temp table. Is there a way to prevent the results from the cursor returning as a recordset? I realize the use of cursors should be avoided if possible, but I've yet to come up with a better solution.

Thanks,
LampknLn45

 

[SQLSister]

instead of the cursor use a set-based delete statement like:
Delete from Table1
where date > '11/21/2002' and date<'1/1/2003'

Replace my where clause of course withthe date conditioin you need.

 

[LampknLn45]

I would love to be able to do that. The problem is that there can and will be more than one date range to evaluate. So, I would need the ability to build that DELETE statement dynamically. Let me explain some more.

I have a set of records (say 1 for every day of the week) for a total of 7 in the result set that deal with hours and pay by day. I store those records in my temp table. I then need to query another table which stores date ranges (these are date ranges during which a person will not receive pay). A person may be penalized 2 days at the beginning of the pay period and 2 days at the end. Hence the two date ranges. These ranges are specific to the individual and must be queried at the time of executing the stored procedure.

So, with that being said, is it possible to prevent the results of my cursor as a recordset?

LampknLn45

 

[SQLSister]

You can use joins to other tables in a delete statement. Here's examples from Book On line

/* SQL-92-Standard subquery */
USE pubs
DELETE FROM titleauthor
WHERE title_id IN
(SELECT title_id
FROM titles
WHERE title LIKE '%computers%')

/* Transact-SQL extension */
USE pubs
DELETE titleauthor
FROM titleauthor INNER JOIN titles
ON titleauthor.title_id = titles.title_id
WHERE titles.title LIKE '%computers%'


You can also use an or statement to account for two differnt ranges like so:
Delete from Table1
where (date > '11/21/2002' and date<'1/1/2003') or
(date > '09/21/2003' and date<'10/06/2003')

YOu can also use variables for the dates if you need to.

 

[LampknLn45]

Okay, I appreciate your help in trying to provide a better way to do this but these are pretty simple SQL statements. My problem however, is not as simple. It is not possible to join my temp table to my date range table. Also, as I stated before, there may be 1 or more date ranges to filter out so I can't very well run a nested select that pulls out 3 (possible) date ranges to evaluate. Here are some sample records:

Let's say I am dealing with employee &quot;123456&quot;
My temp table holds these records (they are specific to the EmployeeID passed to the stored procedure - ID not stored in temp table)

ReportDate JobCode Pay
09/29/2003 Job 1 5.87
09/29/2003 Job 2 6.00
09/30/2003 Job 3 4.54
10/01/2003 Job 4 7.34
10/02/2003 Job 5 2.15
10/03/2003 Job 6 8.17
10/04/2003 Job 7 8.00

My date range table holds these records
EmployeeID FirstDay LastDay Reason
001212 09/25/2003 10/01/2003 Blah
003434 09/28/2003 10/02/2003 Blah
123456 09/28/2003 09/30/2003 Blah
123456 10/03/2003 10/05/2003 Blah

So there are my two tables. Now, I call my stored procedure which takes a day in the week and employee ID as inputs.

Let's say:
exec proTest '9/30/2003 4:30 PM', '123456'

The first thing my procedure does is calculate the start and end dates of the week. In this case:
Begin date: 9/28/2003 12:00:00 AM
End date: 10/4/2003 11:59:59 PM

Next, I do some calculations to create the records which will go into my temp table.

Now I have both tables ready to go. I need to delete from my temp table those records where the ReportDate falls between any relevant date ranges stored in my date range table. Going on the records I've provided, I have two such relevant ranges:
9/28/2003 - 9/30/2003
10/3/2003 - 10/5/2003

I need to compare these date ranges with what is in my temp table. The ultimate result will leave the following records in my temp table:

ReportDate JobCode Pay
10/01/2003 Job 4 7.34
10/02/2003 Job 5 2.15

The best way I could think to do this is with a cursor. The cursor works and when I run the procedure in Query Analyzer I get the desired results (in the second recordset). But the first recordset the SP returns is that of the cursor. I don't want my cursor select statement to return a result set to the calling application. Is there a way to do this similar to
SET NOCOUNT ON?

Thanks for reading,
LampknLn45

LampknLn45

 

[JamesLean]

Using the two tables and data you have provided, you can delete the necessary rows from the temp table using a query something like this:

DELETE #temp
FROM #temp t JOIN range r ON t.reportdate BETWEEN r.firstday AND r.lastday

--James

 

[LampknLn45]

Well, that DELETE statement worked just fine and was much easier than a cursor. Thanks to JamesLean and SQLSister my problems have been solved.



LampknLn45

 

[LampknLn45]

After reading the previous posts again, I feel like I owe SQLSister an apology for not seeing what she was trying to show me. Her advice should have been just as helpful as JamesLean's --- I just didn't read it that way. Keep up the good work. Thanks again.

LampknLn45

 

[SQLSister]

Hey you don't owe me an apology. Sometimes we are all so focused on one solution, that we don't see another one.