0) You don't need to store the "full project number" string. You can build it when you query the database to retrieve the desired data.
1) You shouldn't create a new project in the database unless certain minimal criteria have been met. This will eliminate the arbitrary need to have a project number of 0.
2) When you submit a project to the database, You can find the last project number by using a query that uses the MAX function and a GROUP BY clause. Group by the year of the date the projects have been submitted, something like this:
DECLARE @nextProject int = (
SELECT max(projectnumber)
FROM projects
WHERE YEAR(projectdate) = @projectYear
GROUP BY YEAR(projectdate)
);
SET @nextProject = ISNULL(@nextProject, 0) + 1;
The reason you have to do it this way is because the project number resets to zero at the beginning of every year (at least, that's what I assume you're going for). This means you can't make the
projectnumber
column an
IDENTITY
column.
3) After determining the next available project number, you can store the project data in the table
INSERT INTO projects (projectnumber, projectdate, [other columns...])
VALUES
(
@nextProject,
GETDATE(),
[other column data...]
);
4) To retrieve projects for a given year, you might do something like this:
SELECT projectnumber,
projectdate,
FORMAT(YEAR(projectdate), '#000#') + FORMAT(projectnumber,'00#') AS projnumbstr,
[other columns...]
FROM projects
WHERE YEAR(projectdate) = @projYear;