Swapping sql query for stored procedure and confused about return value

Question

Hi,

This is a bit of code in one of the pages I am reviewing and trying to update. What I am trying to do is swap the inline query for stored procedures. This is the bit of code:

numOfDelRec = 1
Do While (numOfDelRec > 0)
cm.CommandText = "DELETE Folder WHERE FolderID IN 
    (SELECT ch.FolderID FROM Folder ch 
    LEFT JOIN Folder p ON ch.ParentID=p.FolderID " & _
    "WHERE p.FolderID IS NULL AND ch.ParentID > 0) 
    AND UserID=" & Session("UserID")
cm.Execute numOfDelRec
Loop

What I am curious about is that the value numOfDelRec comes back and is used to continue looping, how do I basically do the same thing with a stored procedure, given that the stored procedure is basically the same as the inline sql?

This is my call to the stored procedure:

AuthConn.Execute("sp_MyFolder_DeleteFolder " & Session("UserID"))

This is Microsoft SQL Server 2005.

Thanks, R.

Answer 1

The SQL looks like its deleting records from a self referencing table. More specifically it looks like its deleting parent records with no children. The numOfDelRec probably holds the number of records affected by the sql, i.e. number of rows deleted. So it looks like it continues to run, deleting parent records without children, until the number of deleted records is 0.

It probably loops like that so if a parent record has no children, its deleted. However if it itself was a child its parent record was not deleted on the first pass but will be caught on the second pass and so on.

You did not mention the flavor of database server you are using, but with sql server you can do a while loop which is described here at MSDN http://msdn.microsoft.com/en-us/library/ms178642.aspx That would probably do the trick.

I'm not sure if that is exactly what your asking, but I believe that is what the code is doing.

Answer 2

You can pass the variable to the execute to get the # of rows affected.

AuthConn.Execute("sp_MyFolder_DeleteFolder " & Session("UserID"),numOfDelRec)

http://www.devguru.com/technologies/ado/quickref/connection%5Fexecute.html

Answer 3

First off: Do not name this stored procedure as

sp_

. "sp" stands for "special procedure", and--in SQL Server--all procedures that begin with "sp underscore" [Stack overflow really doesn't want me to type an underscore character where I need to] get special handling. There are times and places to make sp_ procedures [hah, that time it worked], but this doesn't look like one of them. Read SQL Books Online for more on this--it's an involved concept; for now, I'd call the procedure "MyFolderDeleteFolder", or "usp_MyFolder_DeleteFolder" [heck with it, please imagine the underscores are where they should be] if you have to have a semi-redundant naming convention.

Moving on from that mangled caveat, here's how I'd transfer this command into a procedure. (I can't test it just now, it may require some minor debugging, and I'd love to turn the LOJ into a NOT EXISTS):

CREATE PROCEDURE MyFolder_DeleteFolder

    @UserId  int

AS

SET NOCOUNT on

DECLARE @RowsDeleted int
SET @RowsDeleted = 1

WHILE @RowsDeleted > 0
 BEGIN
    --  Loop until a call to DELETE does not delete any rows
    DELETE Folder
     WHERE FolderID IN 
        (SELECT ch.FolderID
          FROM Folder ch 
           LEFT JOIN Folder p
            ON ch.ParentID = p.FolderID 
          WHERE p.FolderID IS NULL
           AND ch.ParentID > 0) 
        AND UserID = @UserID

    SET @RowsDeleted = @@rowcount
 END

RETURN 0

Answer 4

I read your question as that you want to drop the whole of your query into a stored procedure for cleaner code.

If that is the case, then the ExecuteNonQuery method returns the number of rows affected.

Try:

cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = "ProcName"

numOfDelRec = 1
Do While (numOfDelRec > 0)
    numOfDelRec = cm.ExecuteNonQuery()
Loop