return number of rows deleted using stored procedure

Question

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.

The stored procedure does not return the number of rows deleted and you *"don't wish to edit the storedProcedure"*. So you can't — Liam, Apr 11 '14 at 10:18
however if that is the ultimate solution then be it.. BUT how? — stackunderflow, Apr 11 '14 at 10:20
If it is not possible to edit stored procedure, I think the solution may be counting the sessions before and after executing command — CuriousPen, Apr 11 '14 at 10:26
Possible duplicate of [How can I get the number of records affected by a stored procedure?](http://stackoverflow.com/questions/1201358/how-can-i-get-the-number-of-records-affected-by-a-stored-procedure) — Liam, Apr 11 '14 at 10:31

score 2 · Answer 1 · answered Apr 11 '14 at 12:52

2

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.

answered Apr 11 '14 at 12:52

Igor

178
3

excellent answer . I learned from it how to pass output param ;).. but still @Liam solved it by only changing 'SET NOCOUNT' to 'OFF' !! – stackunderflow Apr 11 '14 at 12:59
I am not a C# guy so I don't know how how C# code reacts in this case, but from DBA side setting nocount off will answer your question incorrectly. Sql server will retern 1 output for all rows affected by your insert into temp table lets say 5 rows were affected "(5 row(s) affected)" then if your cursor runs 5 times sql server will say 5 times "(1 row(s) affected)". If C# reads only las messasge your count will be off. – Igor Apr 11 '14 at 13:20
your comment is strange , bec. i tested it and 4 rows deleted and the returned number from the storedProcedure was 4. also when no row deleted it gives me 0, while it was giving always -1. Also notice that is not related to C#. C# will buble up what the procedur returns – stackunderflow Apr 11 '14 at 14:23

return number of rows deleted using stored procedure

1 Answers1