Post reply

Row Count of Insert stmt with sp

  • November 29, 2004 at 6:18 am

    #63572

    Hi,

    I need a rowcount of each executed statement.

    I have this statement in while loop that reads some tables based on user input and populates temp table with results:

    INSERT INTO tmpCDR_Hold_Table Execute sp_executesql @resQuery

    IF @@ROWCOUNT = 0

       Print 'Warning! No records where found in table ' + @strTableName + ' Continue running on the next table... '

      

    The INSERT part is working fine, but @@ROWCOUNT part always returns 0. Why? I am thinking that it is because that sp_executesql doesn't return rowcount value. Is there an easy fix or maybe another way of doing this?

    Note: That query statement is constructed dynamically based on user input (e.g. date ranges, table name, etc.), so I have to use sp_executesql or EXEC(@reQuery) to run it.

    Appreciate any help. Thanks.

     

  • November 29, 2004 at 6:45 am

    #531705

    Based on your post

    @resQuery must be a select statement

    Following the INSERT @@ROWCOUNT will contain the number of rows inserted

    When you say that it is always zero, how do you know, is the message from the print statement is always shown? Are you sure that @resQuery is returning rows?

    Be aware that IF affects @@ROWCOUNT, if you want to retain the value that set an int variable to @@ROWCOUNT

    Far away is close at hand in the images of elsewhere.
    Anon.

  • November 29, 2004 at 6:55 am

    #531707

    David,

    INSERT INTO tmpCDR_Hold_Table Exec( @resQuery) SET @rCounter = @@Rowcount

    It worked. Thanks a lot. That IF statement was messing around, I didn't know that it affects.

     

  • November 29, 2004 at 6:56 am

    #531709

    Hi Jonas, David,

    I don't see why it isnt working either but one easy workaround would be to do some form of a count within the dynamic SQL and return both:-

    Execute sp_executesql @resQuery, @RowCount

    That way you wouldn't have to rely on @@ROWCOUNT.

    Not ideal but...............

    Have fun

    Steve

    We need men who can dream of things that never were.

  • November 29, 2004 at 10:10 am

    #531752

    You mean:

    INSERT INTO tmpCDR_Hold_Table EXECUTE sp_executesql @resQuery, @@Rowcount, @@error

    sp_executesql only takes sp_executesql [@stmt =] stmt

    However this works: INSERT INTO tmpCDR_Hold_Table EXECUTE sp_executesql @resQuery SET @rCounter = @@Rowcount SET @errCode = @@Error 

    But only for SET @rCounter = @@Rowcount, it doesn't store error code, but if I switch the two it stores error code but not row count!

    Does anyone know work arround, so that both variables would be stored?

    Thanks 

      

  • November 29, 2004 at 10:27 am

    #531755

    I found the answer myself. Here is to everyone to share:

    INSERT INTO tmpCDR_Hold_Table EXECUTE sp_executesql @resQuery SELECT @errCode = @@Error, @rCounter = @@Rowcount

  • November 29, 2004 at 10:27 am

    #531754

    Hi Jonas, 
     
    sp_executesql only takes sp_executesql [@stmt =] stmt
     
    You need to read a little more on the use of this sproc matey:-
     
    SET @SQL = N'SET @TableID = (SELECT TPK FROM Tables WHERE TableName = ''' + @TableName + ''')'

     EXEC sp_executesql @SQL, N'@TableID int OUTPUT', @TableID OUTPUT

     
    Its one of the only ways to get one or more output parameters when using EXEC within a sproc.
     
    Best regards  

    Steve

    We need men who can dream of things that never were.

  • November 29, 2004 at 10:36 am

    #531757

    Jonas,

    Looks like we where both typing at the same time.......

    Have fun

    Steve

    We need men who can dream of things that never were.

  • November 29, 2004 at 11:05 am

    #531762

    Thank You. Yes you are right I just newbie. I should have checked sproc myslef not on online books.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply