Introduction

In this post I want to show, how to Handel exception by sql server stored procedure.

View Image Exception Message

View Image Success Message

View

@model MVC_ExceptionHandling_With_SP.Models.Employee

@{
    Layout = null;
}

<script>
    document.title = 'Exception Handling';
</script>

@using (Html.BeginForm())
{
    <div style="margin: 100px 0 0 750px; border: 1px solid #1e1ced; width: 20%; padding-left: 50px;">
        <p style="font-size: 30px;">Exception Handling</p>
        <div>
            Enter Name: @Html.TextBoxFor(m => m.Name, new { placeholder = "Name" })
            <input type="submit" value="Submit" style="background-color: #695a5a; color: #fff; padding: 5px;" />
        </div>
        <p style="color: #3aa549; font-weight: bold;">@ViewBag.SuccessMsg</p>
        <p style="color: #f00; font-weight: bold;">@ViewBag.ErrorMsg</p>
    </div>
}

Controller

using MVC_ExceptionHandling_With_SP.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace MVC_ExceptionHandling_With_SP.Controllers
{
    public class TestController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }

        [HttpPost]
        public ActionResult Index(Employee emp)
        {
            using (Entities db = new Entities())
            {
                try
                {
                    var result = db.getEmployee(emp.Name).Single();
                    ViewBag.SuccessMsg = "Name: " + result.Name + ", Salary: " + result.Salary;
                }
                catch (Exception ex)
                {
                    var errorMsg = ex.InnerException.Message;
                    ViewBag.ErrorMsg = errorMsg;
                }
            }
            return View();
        }
    }
}

Stored Procedure

CREATE PROCEDURE getEmployee
@name nvarchar(100)
AS
	DECLARE @Error_MSG nvarchar(50)
BEGIN
	IF NULLIF(@name, '') IS NULL
	BEGIN
		SET @Error_MSG = 'Please enter name properly!'
	END
	Else 
	BEGIN
		BEGIN
		IF EXISTS (select * from Employee where Name = @name)
		BEGIN
			select * from Employee where Name = @name
		END
		ELSE
		BEGIN
			SET @Error_MSG = 'Sorry, Name that you have enter does not exist!'
		END
		END
	END
	RAISERROR (@Error_MSG, 11,1)
END

Download

You can download this application zip file here - 5.8 MB

Conclusion

Guys I show how to handle exception by sql server stored procedure. If you facing any other problem please leave a comment.