how to write a if else statement to insert query for showing the error in stored procedure

Question

how to write a if else statement to insert query for showing the error in stored procedure

below is my stored procedure.I want to show a error message when already inserted student_id is insert again..Student_id is primary key so its show error in my code but i dono how to get that error and show ....how to do friends plz help me.....

ALTER PROCEDURE [dbo].[spinsertstudentapplication] 
    -- Add the parameters for the stored procedure here
    @Student_id             nvarchar(50),   
    @Select_Country         nvarchar(50),
    @Select_State           nvarchar(50),
    @Select_Franchise       nvarchar(50),
    @Select_Sensei          nvarchar(50),
    @Enter_Student_Name     nvarchar(50),
    @Enter_Student_Address  nvarchar(50),
    @Students_Father_Name   nvarchar(50),
    @Student_DOB            datetime,
    @Gender                 bit,
    @Group                  nvarchar(50),
    @Enter_Kyu              nvarchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here 
    insert into StudentApplication(Student_id,Select_Country,Select_State,Select_Franchise,Select_Sensei,Enter_Student_Name,Enter_Student_Address,Students_Father_Name,Student_DOB,Gender,[Group],Enter_Kyu)values(@Student_id,@Select_Country,@Select_State,@Select_Franchise,@Select_Sensei,@Enter_Student_Name,@Enter_Student_Address,@Students_Father_Name,@Student_DOB,@Gender,@Group,@Enter_Kyu)

END

Answer 1

You can add an output parameter at the top of your SP:

@ErrorOutput INT OUTPUT

Then add:

IF EXISTS (SELECT 1 FROM StudentApplication WHERE Student_id=@Student_id)
    SET @ErrorOutput = -1;
    RETURN @ErrorOutput;
ELSE
    -- Insert statement

Answer 2

As Dean 'codeka' Harding mentioned in the comment, it would be helpful to know how you're calling the stored proc. But here are some general suggestions.

First, a common convention for stored procedures is to return 0 on success and non-zero value on error (you can use an output parameter for error codes, but it's kind of redundant).

Second, before attempting to insert a value, you should check if it already exists. E.g. here is some pseudo code:

if exists (select 1 from StudentApplication where Student_ID = @Student_ID)
begin
  raiserror('Student ID already exists.', 16, 1)
  return 1 -- Your caller would need to know that 1 identifies existing record
end

Notice that in this example, T-SQL code exits after calling raiserror, so you need to handle this as an exception if you call the stored proc from C#/VB.NET/etc. Alternatively, you can omit the raiserror call and just have the stored proc return an expected (by the client) error code.

Then there is still a minor possibility that a duplicate would be inserted, but I think that it would generate a fatal error that you would need to handle in the client code (error handling is dependent on the client; in C#, you will probably get a SqlException which you can query for specific code).

Another option would be to put a transaction around the code that checks for existing record and then inserts a new one.

If you want to handle error in C#, you need to take care of two things. First, check the return code and process non-zero values accordingly (the C# client and stored proc have to agree on the meaning of each error code). Then, you also need to handle SqlExceptions. The State and Number properties of the SqlException object can help you identify the problem. Keep in mind that for error messages defined on the fly (as in my example), Number will alway return 50,000 (I think).

Answer 3

You can add an output parameter(int/bit) in Sp and set value to that parameter whether record exist or not. Then you can check the output parameter value in Front End. Below I've added an output parameter @RecordExist as bit and setting the value 1 when record already exists otherwise setting 0. In front end you can get the Parameter value from SqlCommand after executing the Sp. (SqlCommand.Parameters["@RecordExist"].Value)

ALTER PROCEDURE [dbo].[spinsertstudentapplication]   
    -- Add the parameters for the stored procedure here  
    @Student_id             nvarchar(50),     
    @Select_Country         nvarchar(50),  
    @Select_State           nvarchar(50),  
    @Select_Franchise       nvarchar(50),  
    @Select_Sensei          nvarchar(50),  
    @Enter_Student_Name     nvarchar(50),  
    @Enter_Student_Address  nvarchar(50),  
    @Students_Father_Name   nvarchar(50),  
    @Student_DOB            datetime,  
    @Gender                 bit,  
    @Group                  nvarchar(50),  
    @Enter_Kyu              nvarchar(50),
    @RecordExist            bit output  -- newly added parameter
 AS  
 BEGIN  
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
     SET NOCOUNT ON;

     -- Insert statements for procedure here 

If Exists (Select * from StudentApplication where Student_id = @Student_id)
   Begin
      Select @RecordExist = 1
      return
   End

Else
  Begin
      insert into StudentApplication (Student_id, Select_Country, Select_State,  Select_Franchise, Select_Sensei, Enter_Student_Name, Enter_Student_Address, Students_Father_Name, Student_DOB, Gender, [Group], Enter_Kyu)

      Select @Student_id, @Select_Country, @Select_State, @Select_Franchise, @Select_Sensei, @Enter_Student_Name, @Enter_Student_Address, @Students_Father_Name, @Student_DOB, @Gender, @Group, @Enter_Kyu

      Select @RecordExist = 0
      return
  End

END