In this post, you will Insert & Load using store procedure SQL Server ASP.NET MVC Angularjs Jquery.Here will cover passing parameter in store procedure and writing the store procedure for insert and load data through parameter.

Steps:

Step-1:

• Create store Procedure for Insert Student information.
• Go to SQL Server 2014 > dbStudentMangeSystem database> Programmability> stored procedures> Select New> stored procedure>Create sp_Insert_Student with writing the below code.

ALTER PROCEDURE [dbo].[sp_Insert_Student]
	-- Add the parameters for the stored procedure here
	 @StudentName nvarchar(max)=null ,
	 
	 @BatchId int=null ,
	 @SectionId int=null ,
	 @DOB datetime=null ,
	 @Gender nvarchar(max)=null ,
	 @Religion nvarchar(max)=null ,
	 @FatherName nvarchar(max)=null ,
	 @MotherName nvarchar(max)=null ,
	 @PresentAddrees nvarchar(max)=null ,


	 @PermanentAddress nvarchar(max)=null ,
	 @AddmittedDate datetime=null ,
	 @Status bit=null ,
	 @StudentIdNO nvarchar(max)=null 	 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	INSERT INTO [dbo].[tblStudent]
           ( StudentName, [BatchId]
           ,[SectionId]
           ,[DOB]
           ,[Gender]
           ,[Religion]
           ,[FatherName]
           ,[MotherName]
           ,[PresentAddrees]
           ,[PermanentAddress]
           ,[AddmittedDate]
           ,[Status],StudentIdNO)
     VALUES
            (@StudentName,@BatchId,@SectionId,@DOB,@Gender,@Religion,@FatherName,@MotherName,@PresentAddrees,@PermanentAddress,@AddmittedDate,
			@Status,@StudentIdNO)

			declare @MaxStudentId int 

	 select	@MaxStudentId= MAX(StudentId) from tblStudent 
	 	declare @Bat int 
	 select @Bat= BatchId from tblStudent  where StudentId=@MaxStudentId
	 


	 	declare @BatName nvarchar(max) 
	 select @BatName= BatchName from tblBatch  where BatchId=@Bat
	   declare @StuIDNO nvarchar(max)
	   select @StuIDNO=   ISNULL(MAX(ISNULL(StudentIdNO,0)),0)+1   from tblStudent 
	   
	   where StudentId=@MaxStudentId and BatchId=@Bat
	  

	  insert into tblUser ([UserName]
           ,[LoginName]
           ,[Password]
           ,[UserType]
           ,[StudentId],ActiveStatus)
		   values(
		   @StudentName,
		  @BatName+ @StuIDNO,
		  @BatName+ @StuIDNO,
		   'Student',
		   @MaxStudentId,1
		   )
	   update tblStudent set StudentIdNO=@StuIDNO where  StudentId=@MaxStudentId
	 END

Step-2:

• Get Student Id No add Dataset GetStudentIdNODAL()   in  StudentDAL.cs class.
• Go to Solution Explorer > DAL Folder> StudentDAL.cs>  in this Class with writing the below code.

        public DataSet GetStudentIdNODAL()
        {
            SqlCommand com = new SqlCommand("sp_GetStudentIDNo", conn);
            com.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(com);
            DataSet dss = new DataSet();
            da.Fill(dss);
            return dss;
        }

Step-3:

• Create store Procedure for Insert Student information.
• Go to SQL Server 2014 > dbStudentMangeSystem database> Programmability> stored procedures> Select New> stored procedure>Create sp_GetStudentIDNo with writing the below code.

create  proc [dbo].[sp_GetStudentIDNo]
as
begin


declare @MaxID int 
select @MaxID= MAX(StudentId) from  tblStudent 

select b.BatchName+  StudentIdNO as StudentIdNO from tblStudent s 
left join tblBatch b on b.BatchId=s.BatchId

 where StudentId=@MaxID

end

Step-4: Run Application.