Query Analyser and Recrodset

[DennisTheMenace]

Can I use Query Analyser from Enterprise Manager to
- execute a stored procedure
- have those records in a recordset
While Not rs.eof
- execute another SP to delete related
in another table based on recordset("idrecord&quot
- execute a third SP to delete the original record
from the the recordset("idrecord&quot
Movenext
loop

I am new to scripts. When I execute the one line script:
execute sp_RetrieveEmptyRecords
It simply displays them in the results window and not a recordset that I can scroll thru. A very simple sample would GREATLY be appreciated.

Thanks! =====================
Dennis B

http://www.e-gunparts.com

Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

 

[BobLoblaws]

The deletion can be done in a single stored procedure without the need for a recordset to loop through. In the second part though, are you wanting the stored procedure to display the records to be deleted, before it deletes them?

 

[DennisTheMenace]

I understand the single SP to delete...

But my first delema is retrieving all of the "empty" records, deleting the associated record in the 2nd table (if found) and then delete the main empty record.

-Dennis =====================
Dennis B

http://www.e-gunparts.com

Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

 

[BobLoblaws]

What do you mean by "empty" record?

 

[DennisTheMenace]

A record was created but all of the fields are null...

How bout we just say:
1) I want to retrieve all records in the table SOUP that have chrSoupType="Chunky"
2) Delete all records in the table BOWLS that have idSOUP
3) Delete that idSOUP from the table SOUP
4) Move to next record and start again

(lunch time... can you tell?) ) =====================
Dennis B

http://www.e-gunparts.com

Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

 

[BobLoblaws]

create procedure sp_LunchTime (@SoupName varchar(25))
as
select * from SOUP where chrSoupType = @SoupName
delete BOWLS where idSOUP = (select idSOUP from SOUP where chrSoupType = @SoupName)
delete SOUP where idSOUP = (select idSOUP from SOUP where chrSoupType = @SoupName)

:-Q

 

[DennisTheMenace]

Thank you!

Will there be a problem if I encounter a BOWLS where there is no idSoup that matches?
>delete BOWLS where idSOUP = _
>(select idSOUP from SOUP where chrSoupType = @SoupName)


=====================
Dennis B

http://www.e-gunparts.com

Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

 

[DennisTheMenace]

Ran fine... found 27,000 records, but then produced this error:

Server: Msg 512, Level 16, State 1, Procedure sp_DeleteLastMonthSoupBowls, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated. =====================
Dennis B

http://www.e-gunparts.com

Remember - YOU ARE UNIQUE!!!... Just like EVERYONE ELSE! ;o)

 

[tlbroadbent]

Try this.

select * from SOUP where chrSoupType = @SoupName

delete BOWLS
where idSOUP IN
(select idSOUP from SOUP
where chrSoupType = @SoupName)

delete SOUP where chrSoupType = @SoupName Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.