CODEWITHSUNDEEP
c#sqlstored-proceduressql-server-2012
stackunderflow
stackunderflow

Reputation: 3873

return number of rows deleted using stored procedure

the following is the ASPState storedprocedure that delete expired sessions. If you have your own server then you can configure it to run automatically. however on shared hosting you have to call it from the code on a timed interval

 ALTER PROCEDURE [dbo].[DeleteExpiredSessions]
        AS
            SET NOCOUNT ON
            SET DEADLOCK_PRIORITY LOW 

            DECLARE @now datetime
            SET @now = GETUTCDATE() 

            CREATE TABLE #tblExpiredSessions 
            ( 
                SessionID nvarchar(88) NOT NULL PRIMARY KEY
            )

            INSERT #tblExpiredSessions (SessionID)
                SELECT SessionID
                FROM [luckysessions].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
                WHERE Expires < @now

            IF @@ROWCOUNT <> 0 
            BEGIN 
                DECLARE ExpiredSessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY
                FOR SELECT SessionID FROM #tblExpiredSessions 

                DECLARE @SessionID nvarchar(88)

                OPEN ExpiredSessionCursor

                FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID

                WHILE @@FETCH_STATUS = 0 
                    BEGIN
                        DELETE FROM [luckysessions].dbo.ASPStateTempSessions WHERE SessionID = @SessionID AND Expires < @now
                        FETCH NEXT FROM ExpiredSessionCursor INTO @SessionID
                    END

                CLOSE ExpiredSessionCursor

                DEALLOCATE ExpiredSessionCursor

            END 

            DROP TABLE #tblExpiredSessions

        RETURN 0

I call it through my code as follow:

SqlConnection con = new SqlConnection("Data Source=xxxxx;Initial Catalog=xxxxx;User ID=xxxxx;Password=xxxx;");
            SqlCommand comm = new SqlCommand("DeleteExpiredSessions", con);
            comm.CommandType = CommandType.StoredProcedure;
            con.Open();
            int deleted = comm.ExecuteNonQuery();
            con.Close();

the problem is that I want to know how many rows the StoredProcedure deleted and the ExecuteNonQuery will return always -1

I don't wish to edit the storedProcedure, however if that is the ultimate solution then be it.

Upvotes: 0

Views: 816

Answers (1)

Igor
Igor

Reputation: 178

ALTER PROCEDURE [dbo].[DeleteExpiredSessions](@ deleted int output) AS SET NOCOUNT ON SET DEADLOCK_PRIORITY LOW 

        DECLARE @now datetime
        SET @now = GETUTCDATE() SET @delted = 0

... DELETE FROM [luckysessions].dbo.ASPStateTempSessions WHERE WHERE Expires < @now SET @deleted = @deleted + @@ROWCOUNT ...

I don't think that youu need the cursor there. Either way the easiest solution would be to just declare a variable and everytime you delete any number of row inside your curosor add @@ROWCOUNT to it. After delete is done you can eiver SELECT it, RETURN it, or you can use it as an output parameter for stored procedure.

Upvotes: 2

Related Questions