INSERT or UPDATE table with Custom auto increment value

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 1073
CREATE TABLE StudInfo 
(StudCode varchar(15), Name varchar(50),Surname varchar(50),RollNo int, 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', 2013, 'BBA', 66

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

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

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

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

DROP TABLE StudInfo
DROP PROCEDURE dbo.SP_InsertStudInfo

Comments or Responses

Login to post response