UPDATE IF EXISTS Query

Question

Hi, I am trying to UPDATE a record if there is a row in the table. After updating a record I would like to return TRUE from my method. I am using the following query. I am using SQL server 2005. How do I know if my SQL query updated the table? Please let me know.

Private Boolean UpdateTable()
{
  string sql = "IF EXISTS(Select A.CNum FROM TABLEA A, TABLEB B WHERE A.CID= B.CID AND   A.CNum is NULL AND CID=@cID) BEGIN ..... END" 

}

Thank you..

Answer 1

Whenever you execute a batch of SQL, you should be notified of how many rows were modified / inserted / updated, either as the return value from your e.g. SqlCommand.ExecuteNonQuery() call:

Private Boolean UpdateTable()
{
    int rowsUpdated = 0;

    string sql = "IF EXISTS(Select A.CNum FROM TABLEA A, TABLEB B WHERE A.CID= B.CID AND   A.CNum is NULL AND CID=@cID) BEGIN ..... END" 

    using(SqlConnection con = new SqlConnection("your-connection-string-here"))
    {
        using(SqlCommand cmd = new SqlCommand(sql, con)) 
        {
           con.Open();
           rowsUpdated = cmd.ExecuteNonQuery();
           con.Close();
        }
    }

    return (rowsUpdated > 0);
}

or you can query the @@ROWCOUNT property in your SQL statement after the UPDATE:

...
BEGIN
   UPDATE ........

   DECLARE @Updated INT
   SELECT @Updated = @@ROWCOUNT
END

You can return that value, or check for a value of larger than zero or whatever you want to do.

Answer 2

You can run your query with Sqlcommand.ExecuteNonQuery and that will return the number of affected rows (1 or 0).