Results 1 to 7 of 7
  1. #1
    RonR Guest

    Unhappy How to get Number of Rows effected by a Query .. SQL

    Hello Everyone,

    I hope someone can help me and I need it quick I am running an update query and then I want to display a count on the screen telling the user how many records were effected. I am using MS SQL.

    Thanks

  2. #2
    BAT Guest

    Default

    Do you want the interface to retrieve that info after the update at a separate time or that doesn't matter ? I am thinking you can do a stored procedure that can take care of update and return the rowcount

  3. #3
    Chand Guest

    Default

    No idea what you are using for interface language but here is a code in php.


    Code:
    $result_id = mysql_query ($query, $conn_id);
    # report 0 rows if the query failed
    $count = ($result_id ? mysql_affected_rows ($conn_id) : 0);
    print ("$count rows were affected\n");

  4. #4
    Chand Guest

    Default

    Oh yes a Stored procedure will be better that way you can just call the function with the values you are updating and get the row count. It won't matter what are you using for interface then. My bad!

  5. #5
    BAT Guest

    Default

    Here is a small code I pust together quickly

    You can change according to your need.


    Code:
    CREATE PROCEDURE UpdateTable @User nvarchar(10)
    AS
    BEGIN
        SET NOCOUNT ON;
        DECLARE @RowCount1 INTEGER
    
        UPDATE Table1 Set Enable_bit = 1 WHERE user = @User
        SELECT @RowCount1 = @@ROWCOUNT
       
    
        SELECT @RowCount1 AS Table1
    END

  6. #6
    Chand Guest

    Default

    Excellent BAT ......

    I should mention that in SQL 2005, it is built in. All you need to do is

    Code:
    UPDATE test_table
        SET Enb_bit=1
        OUTPUT INSERTED.* -- INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed 
    WHERE
        user > @user
    then use OUTPUT COUNT(INSERTED.*)

  7. #7
    RonR Guest

    Default

    WOW That was quick !! Thank you guys ....
    Chand,I am not using 2005 so I think i will just use rowcount.
    BAT, Perfect ...... and thank you.

    Really aprreciate you getting back so quickly.

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •