Handle Error Message In SQL Stored Procedure

[srinidelite]

HI

I would Like to Know how can Display the error Message When Executing the SP ..

and the Stored Procedure doesn't having any value to Fire the Event ..

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		<sabari,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER procedure [dbo].[USP_Delete](
@name varchar(100)
)
as 
  begin
 begin try
   begin transaction
 
	delete from custo where name=@name


	
commit transaction
end try
begin catch
  rollback transaction
 SELECT    
    ERROR_NUMBER() AS ErrorNumber,    
    ERROR_SEVERITY() AS ErrorSeverity,    
    ERROR_STATE() AS ErrorState,    
    ERROR_PROCEDURE() AS ErrorProcedure,    
    ERROR_LINE() AS ErrorLine,    
    ERROR_MESSAGE() AS ErrorMessage;  
end catch
end

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using TMSCOMMON;

namespace TMSCOMMON
{

    public partial class Default2 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void Button2_Click(object sender, EventArgs e)
        {
            BLayer objblayer = new BLayer();
            DataTable dt = new DataTable();
            dt = objblayer.Delrecd(TextBox1.Text);
            int success = dt.Rows.Count;
            if (success != 0)
            {
                lblerror.Text = "Delete Suc";
            }
           

        }
    }
}

in the above code will work only if the Data has passed is valid and in the DB if anything match with that is doing "Deletion" .... certainly we checked with empty strings and it returns the Same Message so I would like to Show the Error that User "entered a empty String"!!!

if it enters empty and trigger the button>> Want to Show in ASP.Net Forms

[ckchaudhary]

Add 2 output parameters to your stored procedure

ALTER procedure [dbo].[USP_Delete](
@name varchar(100), @success int output, @error varhcar(500) output
)

then set the respective values in try catch block

begin try
   begin transaction
	delete from custo where name=@name;
   SET @success = 0; -- 0 means query executed successfuly
commit transaction
end try
begin catch
  rollback transaction;
  SET @success = 1;
  SELECT @error = ERROR_MESSAGE();

and in your code first check if the value of output parameter (after executing the sp) is 0. else display the error message

int success = Convert.ToInt32(outparam1.value);
if(success == 0)
{
 lblerror.Text = "Delete Suc";
}
else
{
 //lblerror.Text = "Error !! Details cudn't be deleted";
 lblerror.Text = outparam2.value.ToString();
}