USE [SchoolDB]

GO

/****** Object:  StoredProcedure [dbo].[SP_Student]    Script Date: 11/18/2021 9:48:54 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[SP_Student]

@StudentId varchar(50)

,@Name varchar(50)

,@Roll varchar(50)

,@OperationType int

AS

BEGIN TRAN

IF(@OperationType = 1) --Insert

BEGIN

SET @StudentId = (SELECT MAX(StudentId) FROM Student) + 1


INSERT INTO Student (StudentId, [Name], Roll)

        VALUES(@StudentId, @Name, @Roll)


SELECT * FROM Student WHERE StudentId=@StudentId


END

ELSE IF(@OperationType = 2) --Update

BEGIN

IF (@StudentId = 0)

BEGIN

ROLLBACK

RAISERROR (N'Invalid Student !!!',16,1);

RETURN

END


UPDATE Student SET [Name]=@Name

  ,Roll=@Roll

WHERE StudentId=@StudentId


SELECT * FROM Student WHERE StudentId=@StudentId

END

ELSE IF(@OperationType = 3) --Delete

BEGIN

IF (@StudentId = 0)

BEGIN

ROLLBACK

RAISERROR (N'Invalid Student !!!',16,1);

RETURN

END


DELETE FROM Student WHERE StudentId=@StudentId

END

COMMIT TRAN