Procedure to INSERT/UPDATE Student Info based of Roll ID availability

CREATE TABLE StudInfo (StudCode varchar(15), Name varchar(50),Surname varchar(50),RollNo int PRIMARY KEY, Address varchar(50),Prequlification varchar(50),Year INT, PrePercentage varchar(50))
GO
CREATE PROCEDURE dbo.SP_InsertStudInfo 
@StudName varchar(50), 
@StudSurname varchar(50), 
@RollNo int, 
@Address varchar(50), 
@Year varchar(50), 
@PreQulification varchar(50), 
@PrePercentage varchar(50) 
AS 
BEGIN 
DECLARE @code VARCHAR(20) =''
 
	SELECT @code = coalesce(MAX( CAST(SUBSTRING(StudCode, 3, 10) AS INT)+1), 1001) FROM StudInfo
	IF NOT EXISTS (SELECT 1 FROM StudInfo WHERE RollNo= @RollNo)
		Insert into dbo.StudInfo(Name,Surname,RollNo,Address, Year, PreQulification,PrePercentage,StudCode) values(@StudName,@StudSurname,@RollNo,@Address,@Year,@PreQulification,@PrePercentage,LEFT(@StudName,1)+LEFT(@StudSurname,1)+@code) 
	ELSE
		UPDATE StudInfo SET 	
			StudCode = CASE WHEN StudCode IS NULL THEN LEFT(coalesce(@StudName, name),1)+LEFT(coalesce(@StudSurname, Surname),1)+@code ELSE StudCode END,
			Name = coalesce(@StudName, name),
			SurName = coalesce(@StudSurName, SurName),
			Address = coalesce(@Address, Address),
			Year = coalesce(@Year, Year),
			PreQulification = coalesce(@PreQulification, PreQulification),
			PrePercentage = coalesce(@PrePercentage, PrePercentage)
		WHERE RollNo = @RollNo
END 
GO
--1st time
EXEC dbo.SP_InsertStudInfo 'samU ', 'varma ', 1, 'Mumbai', NULL, 'BBA', 66

--2nd time
EXEC dbo.SP_InsertStudInfo 'tina', 'sharma', 2, 'Delhi', NULL, 'BCA', 65
SELECT * FROM StudInfo

OUTPUT
StudCode	Name	Surname	RollNo	Address	Prequlification	Year	PrePercentage
sv1001	        samU 	varma 	1	Mumbai	BBA	NULL	66
ts1002	        tina	sharma	2	Delhi	BCA	NULL	65

In above executions the ID is new to the table, SO it will do INSERT operation

-- 3rd time  UPDATE firstname & lastname of 2nd roll no
EXEC dbo.SP_InsertStudInfo 'tinaUpdate', 'sharmaUpdate', 2, 'Delhi', NULL, 'BCA', 65

SELECT * FROM StudInfo
OUTPUT:
StudCode	Name	Surname	     RollNo	Address	Prequlification	Year	PrePercentage
sv1001	        samU 	   varma 	1	Mumbai	BBA	NULL	66
ts1002	        tinaUpdate sharmaUpdate	2	Delhi	BCA	NULL	65


Here, the Roll Id =2 is already in table, So SP will do UPDATE operation

DROP TABLE StudInfo
DROP PROCEDURE dbo.SP_InsertStudInfo