Stored procedure for auto generating roll no?

Question

Guys I have a stored procedure for adding a new student to database. The table to which its adding the student has ID, Name, Batch, Department and roll no columns.

Now what I want to do in a stored procedure is that I want to take the batch year entered by user (for example, 2010) and the department entered (Such as CE for computer engineering) and then auto generate the roll no column based on that. (For example, 2010-CE-1 and then for next 2010-CE-2 and so on...) but if the user selects a different year or batch, it should continue the roll no from that sequence.

For Example :

Batch : 2010 | Depart : CE | "auto-generated roll no" : 2010-CE-01
Batch : 2010 | Depart : CE | "auto-generated roll no" : 2010-CE-02
Batch : 2012 | Depart : CE | "auto-generated roll no" : 2012-CE-01
Batch : 2010 | Depart : CS | "auto-generated roll no" : 2010-CS-01
Batch : 2010 | Depart : CS | "auto-generated roll no" : 2010-CS-02

I hope you get the point from above example...now how do I go about doing that? I can concatenate batch + depart but how should I increment the roll no (based on the previous entries in db)?

Answer 1

You need to do two things:

1. Create a table to hold the roll numbers you've already given out - something like this:

   CREATE TABLE dbo.RollNumbers
   (
      ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
      RollYear INT,
      DepartmentID CHAR(2),
      LastRollNo INT
   )
   

   This table will hold the last issued LastRollNo number for each RollYear and DeptId.

2. You need a stored procedure that returns you a new roll number to use, given a year and a department - something like this:

   CREATE PROCEDURE dbo.GetRollNo (@Year INT, @DeptId CHAR(2))
   AS BEGIN
         DECLARE @Output INT
   
         -- if we already have an entry for that year & department
         -- return the next value in a single atomic UPDATE statement
         IF EXISTS (SELECT * FROM dbo.RollNumbers 
                    WHERE RollYear = @Year AND DepartmentID = @DeptId)
         BEGIN
           -- temporary table variable to hold output values
           DECLARE @NewRollNo TABLE (RollNo INT)    
   
           UPDATE dbo.RollNumbers
           SET LastRollNo = LastRollNo + 1
           OUTPUT INSERTED.LastRollNo INTO @NewRollNo
           WHERE RollYear = @Year AND DepartmentID = @DeptId
   
           -- Select the one and only value from @NewRollNo into @Output
           SELECT TOP (1) @Output = RollNo FROM @NewRollNo
        END
        ELSE
        BEGIN
           -- if no entry existed - create a new entry, return "1"
           INSERT INTO dbo.RollNumbers(RollYear, DepartmentID, LastRollNo)
           VALUES (@Year, @DeptId, 1)
   
           SELECT @Output = 1
        END
   
        RETURN @Output
   END 
   

Now you can call this stored procedure before you insert your data, and get back the next roll number to use:

DECLARE @NextRollNo INT

EXEC @NextRollNo = dbo.GetRollNo @Year = 2014, @DeptId = 'CE'

SELECT @NextRollNo

and voila - you have your next, valid RollNo for a given (Year, DeptId) and you can use that in your INSERT