How can I see if a SQL query has properly executed without errors?

Question

I am creating a program which detaches a database.

How can I check to see whether or not the stored procedure has run without errors so I can confirm to the user it is fully finished.

I'm using a try catch around the execution, but i'm not 100% certain this will return all internal errors?

My concern is that if I write:

try
{
    cmd.ExecuteNonQuery();
    MessageBox.Show("it has finished");
}
catch (exception ex)
{
ex.ToString();
}

Isn't reliable

my Command:

 return "sp_detach_db '" + dbType[db] + "', 'true'";

It is reliable. If an exception is thrown, it will catch it. — stuartd, Apr 13 '17 at 14:44
Wouldn't any unhandled exceptions in the PL/SQL bubble up to .NET? Assume a try/catch will take care of it... — Nick, Apr 13 '17 at 14:45
Have you tried it? Write a stored procedure that divides 1 by 0, call it, and see what happens? — Tipx, Apr 13 '17 at 14:51
ExecuteNonQuery will return the number of row affected. Depending on what your stored procedure does you might want to check you have more than 0 rows affected — Ken Tucker, Apr 13 '17 at 14:51
For `sp_detach_db`, you might want to also check the procesure's return value - 0 (success) or 1 (failure) — stuartd, Apr 13 '17 at 14:59

score 2 · Accepted Answer · edited May 23 '17 at 12:26

Your original code is fine (try/catch)...the string you're passing in is the tricky part of what you're trying to do. I would look at this answer to figure out of the command returned a 0 or 1........ if the return value is 1, then you will throw a new exception.

Calling stored procedure with return value

As per the documentation: you are after the RETURN_CODE.

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-detach-db-transact-sql

Return Code Values
0 (success) or 1 (failure)
Result Sets
None

pseudo c# code

using (SqlConnection conn = new SqlConnection("MyString"))
using (SqlCommand cmd = conn.CreateCommand())
{
    cmd.CommandText = string.Format("sp_detach_db '{0}', 'true'", myDatabaseName); /* where myDatabaseName is a string */
    cmd.CommandType = CommandType.Text;


    var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);
    returnParameter.Direction = ParameterDirection.ReturnValue;

    conn.Open();
    cmd.ExecuteNonQuery();
    object result = returnParameter.Value;
    int resultInt = Convert.ToInt32(result);
    if( 0 != resultInt )
    { throw new ArgumentOutOfRangeException("detach failed");}
}

Look at Gary's answer (same question I posted earlier) as well. http://stackoverflow.com/questions/6210027/calling-stored-procedure-with-return-value/25674912#25674912 I think that's what I had in-mind when I posted the code. — granadaCoder, Apr 13 '17 at 15:41

score 1 · Answer 2 · answered Apr 13 '17 at 14:52

1

If your stored procedure is able to raise an exception, you can catch it in your try-catch block.

create procedure MySp
as
begin

      <some operation>

      if @@error <> 0
      begin
            rollback;
            raiserror('error message', 16, 1);
            return -1;
     end

     return 0;
end

answered Apr 13 '17 at 14:52

McNets

10,352
3
32
61

please see my new comments for the SP. It's a default SQL one which I don't want to amend if possible as this will be used on 100's of machines. – SCramphorn Apr 13 '17 at 14:57
1

This code has a rollback. The template has no begin-tran or commit-tran. IMHO, this is writing code for the sake of writing code...the stored procedure could call the sp_detach_db, but the contents of the stored procedure could be as simple as "return sp_detach_db (args)".. the extra @@error check is unnecessary. Any exception would simply bubble up to c#... and the c# code still needs to find the return_code.....(as per my answer) Again, IMHO. – granadaCoder Apr 13 '17 at 15:40

score 0 · Answer 3 · edited May 23 '17 at 10:30

0

You can check the value of ExecuteNonQuery(), which returns an Int for Rows Affected You can also parse the Status Message from SQL as well to check error values

edited May 23 '17 at 10:30

Community

1
1

answered Apr 13 '17 at 14:50

Mad Myche

1,075
1
7
15

How can I see if a SQL query has properly executed without errors?

3 Answers3