Agfdhyk

I have created a Student table in my database, and I need to use a stored procedure to generate unique StudentID when inserting new rows, which should start at 005 up to and including 025.

create procedure FillStudent

as

    insert into Student(StudentID)

    values (005)

go

But then I am thinking, what if it becomes 2 digits? Would it automatically do 010? Or would it be 0010? If it would be the last one, What's the best approach to make it so like '010'? I am using SQL Server.

Play with this code:

CREATE SEQUENCE [dbo].[StudentsID] 

START WITH 5

INCREMENT BY 1

MAXVALUE 25;



CREATE TABLE [dbo].[Student]

(

    [StudentID] CHAR(3)

);





GO



CREATE PROCEDURE [dbo].[FillStudent]

AS

BEGIN;

    INSERT INTO [dbo].[Student] ([StudentID])

    VALUES (RIGHT(NEXT VALUE FOR  [dbo].[StudentsID] + 1000, 3));

END;

GO



GO



EXEC [dbo].[FillStudent];

EXEC [dbo].[FillStudent];

EXEC [dbo].[FillStudent];

EXEC [dbo].[FillStudent];

EXEC [dbo].[FillStudent];

EXEC [dbo].[FillStudent];

EXEC [dbo].[FillStudent];

EXEC [dbo].[FillStudent];

EXEC [dbo].[FillStudent];

EXEC [dbo].[FillStudent];

EXEC [dbo].[FillStudent];

EXEC [dbo].[FillStudent];



SELECT *

FROM [dbo].[Student];







DROP TABLE [dbo].[Student];

DROP PROCEDURE [dbo].[FillStudent];

DROP SEQUENCE [dbo].[StudentsID];

The idea is to use sequence object.

If you need to use identity column you can create the table with identity column, computed column (this is the value you need) and a check constraint to stop adding records after id = 25 is reach. Execute the script below:

CREATE TABLE [dbo].[Student]

(

     [ID] TINYINT IDENTITY(5, 1)

    ,[StudentID] AS RIGHT([ID] + 1000, 3) PERSISTED

    ,CONSTRAINT CK_Student CHECK  ([ID] < 26)

);



INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;

INSERT INTO [dbo].[Student] DEFAULT VALUES;





SELECT *

FROM [dbo].[Student]



DROP TABLE [dbo].[Student]; 

You can see after the record with ID = 25 you are getting an error.

Just make your StudentID column an identity column. In your case set the seed value as 5 and increment as 1.

[StudentID] [int] IDENTITY(5,1) NOT NULL

Then SQL Server will automatically populate that column for you. Just ignore that column in your INSERT statement.

Never try to add leading zeros into the actual data stored in the table. That is not a good approach. Recommended way is to achieve such requirements via formatting the data upon retrieval. You can achieve it as follows.

REPLACE(STR([StudentID],3),' ','0')