T-SQL Stored Procedure with OUTPUT Variable

Create a stored procedure in SQL Server that has an Output variable and uses Try Catch Throw for error handling. The complete article is located at SQL_TryCatchThrow.php.

CREATE PROC Person.usp_EmailInsert
 ( @busID int=NULL,
   @email nvarchar(256)=NULL,
   @msg varchar(255) OUTPUT
  )
 AS

 BEGIN TRY
  INSERT INTO Person.EmailAddress(BusinessEntityID, EmailAddress, rowguid, ModifiedDate) VALUES
  (@busID, @email, newID(), getdate());
 END TRY
 BEGIN CATCH
  THROW;
 END CATCH
 Set @msg = 'Code execution complete. ' + convert(varchar, getdate(), 0);
 GO

C# Console Application to Capture SQL OUTPUT Variable

Create a new C# Console Application in Visual Studio 2015 that will handle any errors thrown back by SQL Server (or in the code itself).

In the upper part of the code, add references to use the Data.SqlClient and the Configuration assemblies.

using System.Data.SqlClient;
using System.Configuration;

Add an AppSetting to the App.Config file for the SQL Connection String.

<appSettings> <add key="sqlconn" value="Data Source=SQLSERVERNAME;Initial Catalog=AdventureWorks2014;Integrated Security=SSPI;" /> </appSettings>

Add code to Main to capture user data entry for the 2 SQL Input variables required by the Stored Proc.

  Console.WriteLine("Please enter the Business Entity ID:");
  arg1 = Console.ReadLine();
  Console.WriteLine("Please enter the email address:");
  arg2 = Console.ReadLine();
  Console.WriteLine("You entered " + arg1 + ", " + arg2);

Add code to make the SQL connection and Command variables as well as the 2 Input Parameters for the connection.

  SqlConnection conn = new SqlConnection(sqlcon);
  cmdstr = "Person.usp_EmailInsert ";
  SqlCommand cmd = conn.CreateCommand();
  cmd.CommandText = cmdstr;
  cmd.CommandType = System.Data.CommandType.StoredProcedure;
  cmd.Parameters.AddWithValue("@busID", arg1);
  cmd.Parameters.AddWithValue("@email", arg2);

Code the Output parameter with the variable name, data type, size, and Direction to capture it, if all goes well without Errors...

  SqlParameter msg = new SqlParameter();
  msg.ParameterName = "@msg";
  msg.SqlDbType = System.Data.SqlDbType.VarChar;
  msg.Size = 255;
  msg.Direction = System.Data.ParameterDirection.Output;
  cmd.Parameters.Add(msg);

Code to open the SQL Connection and execute the code for the stored proc to run.

  conn.Open();
  cmd.ExecuteNonQuery();
  mesg = msg.Value.ToString();

Add code for the Regular Exception and a SQL Exception in Catch blocks

  catch (SqlException sqlEx)
  {
   Console.WriteLine("SQL Exception Thrown: " + sqlEx.Message);
  }
  catch (Exception ex)
  {
    Console.WriteLine("Exception in Code: " + ex.Message);
  }

Add a Finally statement to capture the Output variable when execution is successful.

  finally {
   Console.WriteLine(mesg);
   Console.ReadLine();
  }

Test the code with Invalid Input to cause the SQL Exception capture block to provide the error message back to the user.

Test the code with Valid input to cause the finally block to execute and provide the Output variable information in the Console window.

Complete code is here:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Configuration;

 namespace CsharpTryCatch
 {
  class Program
  {
    public static string sqlcon = ConfigurationManager.AppSettings["sqlConn"].ToString();

    static void Main(string[] args)
    {
     string mesg = "";
     try
     {
       string arg1;
       string arg2;
       string cmdstr = "";

       Console.WriteLine("Please enter the Business Entity ID:");
       arg1 = Console.ReadLine();
       Console.WriteLine("Please enter the email address:");
       arg2 = Console.ReadLine();
       Console.WriteLine("You entered " + arg1 + ", " + arg2);

       SqlConnection conn = new SqlConnection(sqlcon);
       cmdstr = "Person.usp_EmailInsert ";
       SqlCommand cmd = conn.CreateCommand();
       cmd.CommandText = cmdstr;
       cmd.CommandType = System.Data.CommandType.StoredProcedure;
       cmd.Parameters.AddWithValue("@busID", arg1);
       cmd.Parameters.AddWithValue("@email", arg2);
       SqlParameter msg = new SqlParameter();
       msg.ParameterName = "@msg";
       msg.SqlDbType = System.Data.SqlDbType.VarChar;
       msg.Size = 255;
       msg.Direction = System.Data.ParameterDirection.Output;
       cmd.Parameters.Add(msg);

       conn.Open();
       cmd.ExecuteNonQuery();
       mesg = msg.Value.ToString();
     }
     catch (SqlException sqlEx)
     {
       Console.WriteLine("SQL Exception Thrown: " + sqlEx.Message);
     }
     catch (Exception ex)
     {
       Console.WriteLine("Exception in Code: " + ex.Message);
     }
     finally {
       Console.WriteLine(mesg);
       Console.ReadLine();
     }
   }
 }
}