Summary

Σε αυτό το επεισόδιο SQL Server in Greek εξηγούμε τα database engine errors και πως μπορούμε να τα χρησιμοποιήσουμε από τις εφαρμογές μας.

Video

Video Demo Code

SQL Script

select 10/0;

select * from sys.messages;

select * from sys.messages
where language_id = 1033;

select * from sys.messages
where language_id = 1032;

select    min(message_id) as min_message_id,
        max(message_id) as max_message_id
from sys.messages;

/*
RAISERROR ( { msg_id | msg_str | @local_variable }  
            { ,severity ,state }  
            [ ,argument [ ,...n ] ] ) [ WITH option [ ,...n ] 
*/

raiserror (9002,17,1);

raiserror ('this is error',17,1)

/*
sp_addmessage [ @msgnum= ] msg_id , [ @severity= ] severity , [ @msgtext= ] 'msg'   
     [ , [ @lang= ] 'language' ]   
     [ , [ @with_log= ] { 'TRUE' | 'FALSE' } ]   
     [ , [ @replace= ] 'replace' ]
*/

exec sp_addmessage 50001,17,N'This is my error';

raiserror (50001,17,1);
raiserror (50001,10,1);

exec sp_cycle_errorlog;

raiserror (50001,17,1) with log;
exec xp_readerrorlog 0,1;

raiserror (50001,10,1) with log;
raiserror ('hello this is my error!!',10,1) with log;
exec xp_readerrorlog 0,1;

/*
THROW [ { error_number | @local_variable },  
        { message | @local_variable },  
        { state | @local_variable } ]   
*/

throw 9002,'log full',1;

throw 50002,'my error',1;

-- state
throw 50002,'my error',1;
throw 50002,'my error',2;


create or alter proc pDemoError @num1 int , @num2 int
as
begin

    declare @num3 int = 0;
    declare @erstate int = 0;

    if ( @num1 = @num2 )
    begin
        --raiserror ('Numbers are equal',10,1);
        --select 0;
        set @erstate=1;
        throw 60000,'Numbers are equal',@erstate;
        return -1;
    end

    begin try
        set @erstate=2;
        set @num3 = @num1 / @num2;
        select @num3;
    end try
    begin catch
        declare @err_number int = ERROR_NUMBER(), 
                @err_msg nvarchar(1024) = ERROR_MESSAGE(),
                @err_severity int = ERROR_SEVERITY(),
                @err_state int = ERROR_STATE(),
                @err_line int = ERROR_LINE();
        --raiserror (@err_msg,@err_severity,@err_state);
        --raiserror (@err_msg,17,@erstate);
        --throw @err_number,@err_msg,@err_state;
        --throw 60000,@err_msg,@err_state;
        throw 60000,@err_msg,@erstate;
    end catch

end
go


exec pDemoError @num1=10 , @num2 = 10

C# Code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;


namespace SQLErrorsTrapping
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }


        private void buttonExecute_Click(object sender, EventArgs e)
        {
            try
            {
                using (SqlConnection cn = new SqlConnection("server=sql2019;database=tempdb;trusted_connection=true;"))
                {
                    try
                    {
                        SqlCommand cmd = cn.CreateCommand();
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = "pDemoError";
                        cmd.Parameters.AddWithValue("@num1", Convert.ToInt32(textBoxNum1.Text));
                        cmd.Parameters.AddWithValue("@num2", Convert.ToInt32(textBoxNum2.Text));
                        cn.Open();
                        MessageBox.Show(cmd.ExecuteScalar().ToString());
                    }
                    catch (SqlException sqlex)
                    {
                        StringBuilder err = new StringBuilder();

                        err.AppendFormat("Error Code\t: {0}\n", sqlex.ErrorCode.ToString());
                        err.AppendFormat("Error Number\t: {0}\n", sqlex.Number.ToString());
                        err.AppendFormat("Error Message\t: {0}\n", sqlex.Message);
                        err.AppendFormat("Error Severity\t: {0}\n", sqlex.Class.ToString());
                        err.AppendFormat("Error State\t: {0}\n", sqlex.State.ToString());

                        MessageBox.Show(err.ToString(),"SQL Server Error");
                    }
                    finally
                    {
                        if (cn.State != ConnectionState.Open) cn.Close();
                        cn.Dispose();
                    }
                    
                }
            }
            
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            
        }
    }
}