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);
}
}
}
}