Hi there

Could anyone help me with the following problem.

I would like to write a stored procedure in SQL Server to delete multiple records from a table. Say, where Level2ID=20.

If I only had one entry of this I would use the wizard in SQL Server to do the following:

CREATE PROCEDURE [sp_level2_delete]
    (@Level2ID [int])

AS DELETE [Content].[dbo].[faq_level2]

WHERE
    ( [Level2ID] = @Level2ID)
GO

And then in Dreamweaver I would have a process page and under 'Bindings', choose Command (stored procedure) which would bring in the following code:

<%

Dim Command1__Level2ID
Command1__Level2ID = ""
if(Request("Level2ID") <> "") then Command1__Level2ID = Request("Level2ID")

%>
<%

set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_Content_STRING
Command1.CommandText = "dbo.sp_level2_delete"
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 3, 4)
Command1.Parameters.Append Command1.CreateParameter("@Level2ID", 3, 1,4,Command1__Level2ID)
Command1.Execute()

        Response.Redirect "../../index.asp"

%>

Now I'd like to apply this theory but to a different table which contains multiple records where Level2ID=20.

How would I write the stored procedure to do this? I'm guessing I need a loop around part of the code but am not sure where.

Any ideas?

Thanks in advance

Lucy

Hi Lucy,

Are you saying you want multiple records deleted from one table, or do you want to execute your code for each record in another table?

In the first scenario, the stored procedure will just do that. When multiple record match the WHERE clause, all of them will be deleted.

For the second scenario, can you provide more info? In that case, I am not sure what you're trying to accomplish exactly....

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Vergeet Me by Doe Maar (Track 3 from the album: Doris Day en andere Stukken) What's This?

Hi Imar

Sorry to be so confusing! I've had a little bit of time to think about this so will try to explain it a little better.

I have 2 tables, 'Level2' and 'Level3'.

'Level2' contains 2 columns, Level2ID and Level2Name.
'Level3' contains 3 columns, Level3ID, Level3Name and Level2ID.

If I want to delete a record from 'Level2' then my stored procedure would be:

CREATE PROCEDURE [sp_level2_delete]
    (@Level2ID [int])

AS DELETE [Content].[dbo].[Level2]

WHERE
    ( [Level2ID] = @Level2ID)
GO

From your reply i'm guessing that if I wanted to delete all records in Level3 by passing through Level2ID then the stored procedure would be:

CREATE PROCEDURE [sp_level3_delete]
    (@Level2ID [int])

AS DELETE [Content].[dbo].[Level3]

WHERE
    ( [Level2ID] = @Level2ID)
GO

This would delete every record in the database that contains the Level2ID i'm passing through (I don't know whether this stored procedure does what I want it to do).

Now, i'd like to combine the stored procedures so that, in one, I can say 'delete all records in Level2 and delete all records in Level3 which contains multiple instances where Level2ID is a specific value'.

I hope this is a little clearer - i'm trying to get as much info as I can down for you so you can see what i'm trying to achieve!!

Any ideas?

Cheers

Lucy

I am not sure, if this is what you are trying to achieve.

This does delete the records from LEVEL3 table first and then LEVEL2 table containing Level2ID = PARAMETER that you pass to the proc.

CREATE PROCEDURE [sp_level2_delete] (@Level2ID [int])
AS
BEGIN TRAN
 DELETE [Content].[dbo].[Level3]
 WHERE ( [Level2ID] = @Level2ID)
 IF @@ERROR<>0
 BEGIN
   ROLLBACK TRAN
   RETURN (1)
 END
 DELETE [Content].[dbo].[Level2]
 WHERE ( [Level2ID] = @Level2ID)
 IF @@ERROR<>0
 BEGIN
   ROLLBACK TRAN
   RETURN (2)
 END
 COMMIT TRAN
GO

@@ERROR is a server variable that contains NON-ZERO value if the delete statement fails for any reason. So when the first DELETE fails it terminates the procedure and doesn't execute the second.

Hope this is what you were trying to achieve. If so give a meaningful name to the SP there (sp_level2_delete).
Cheers!

-Vijay G

Don't worry, Lucy. It's perfectly clear now. And I think Vijay G has given you the right answer.

All you need to do is call two separate DELETE statements, both taking the LevelID as a parameter.

The stuff about BEGIN TRAN, ROLLBACK TRAN etc Vijay added is to make sure that either both statements complete successfully, or both fail. In case of an error, you won't end up with orphan elements in the level3 table.....

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Thanks Imar,
For mentioning about the BEGIN TRAN, ROLLBACK/COMMIT TRAN, I forgot to mention about that in my post.:)

-Vijay G