stored procedure problem

hi all
i have a problem in the sql server procedure. can anybody correct me

my procedure is like this

CREATE PROCEDURE SP_Delete_DocumentTypeInstance
(
@MaptoInternalID int,
@MapfromInternalID int
)
AS

Set nocount on

Declare @MapID int

select @MapID = (select MaptoInternalID from DocumentTypeInstance where MaptoInternalID=@MaptoInternalID)
While (select count(*) from DocumentTypeInstance where MaptoInternalID =@MaptoInternalID) > 0
begin
select @MapID =(select top 1 MapID from Map where MapID = @MapID)
Exec sp_delete_Map @MaptoInternalID,@MapfromInternalID
end

Delete From DocumentTypeInstance
where
MaptoInternalID = @MapID

Set nocount off
GO


i must delete all rows in the documenttypeinstance table but that table has connection with another table named map
the tables are
map table
mapid,mapname,mapurl (mapid primary key)
and another table is documenttype instance
it is consisting fields like this
documenttypeinstance id(primary key)
documenttypeid(foreign key with document table documenttypeid)
docspec
maptointernalid(foreign key with map table mapid)
mapfrominternalid(foreign key with map table mapid)

and i must delete from documenttypeinstance. i wrote another procedure to delete from map also i am sending that procedure also correct that also please help me some body

CREATE PROCEDURE SP_Delete_Map
(
@MaptoInternalID int,
@MapfromInternalID int
)
AS

Set nocount on

Declare @MapID int

select @MapID =(select MaptoInternalID from DocumentTypeInstance where MaptoInternalID=@MaptoInternalID)



Delete From DocumentTypeInstance
where
MaptoInternalID = @MapID

delete from Map
where
MapID=@MapID
Set nocount off
GO

please friends these procedures worked for me properly at first but they are not working now bcoz i have added rows in both tables. The error says that there are more than one value for selection.

sai gopal

Check to make sure that the field 'MaptoInternalID' is unique other wise you'll need more logic in :

[ select @MapID = (select MaptoInternalID from DocumentTypeInstance where MaptoInternalID=@MaptoInternalID) ].

to ensure that there will only ever be one row returnd. Else try looking at IN or EXISTS

not too sure about the line of code :

[While (select count(*) from DocumentTypeInstance where MaptoInternalID =@MaptoInternalID) > 0]

Also you should encompass all tasks in one Stored Procedure within a large transaction - checking after every step that no errors have occurred, if so then you will need to rollback the entire transaction to mantain complete data intergity.

Thank you for responding to my problem ...... i will check your suggestion and i will give you reply again......
Thanks a lot.

sai gopal