CREATE PROCEDURE crttdID(@RowCount int, @StuNum varchar OUTPUT)
AS
BEGIN
DECLARE @Dateof_Admission datetime
SET @RowCount = (SELECT COUNT(1) from Student_Details where datepart(year,
Dateof_Admission)= datepart(year, @Dateof_Admission))
SET @StuNum = (select 'T' + right (DATEPART(yy, current_timestamp), 2)+ '-'
+ RIGHT('00' + CAST(@RowCount AS varchar(3)), 3))
END;a put this in the sql file and tested it but only "T" will print and one more thing i need to insert the StudentID to the Student_Details table without creating another table 2009/11/20 Raghupathi Kamuni <[email protected]> > select > RIGHT('00' + CAST(@RowCount AS varchar(3)), 3) > > Maximum students allowed per year 999 ONLY !! > > > On Fri, Nov 20, 2009 at 9:37 AM, rbr <[email protected]> wrote: > >> So, as you can see, there are many ways to solve this issue. I am >> assuming you are trying to generate the Student_ID >> on insert? A stored procedure would be best if possible. >> >> In that case, I would do something very similar to what Cerebrus >> recommended: >> >> Begin >> >> DECLARE @RowCount int >> DECLARE @StuNum varchar >> >> SET @RowCount = (SELECT COUNT(1) from Student_Details where datepart >> (year, Dateof_Admission)= datepart(year, @Dateof_Admission)) >> >> SET @StuNum = (select 'T' + right(DATEPART(yy, current_timestamp), 2)+ >> '-' + RIGHT('00' + CAST(@RowCount AS varchar(3)), 3)) >> >> INSERT Student_Details >> ( >> Student_ID, >> Fname, >> Lname, >> Full_Name, -- >> Address, >> DOB, >> ContactNO, >> email, >> additional_detail, >> Course_ID, >> Dateof_Admission, >> Dateof_Graduation >> ) >> VALUES >> ( >> @StuNum, >> @Fname, >> @Lname, >> @Full_Name, -- >> @Address, >> @DOB, >> @ContactNO, >> @email, >> @additional_detail, >> @Course_ID, >> @Dateof_Admission, >> @Dateof_Graduation, >> ) >> >> END >> >> Let me know if this workd for you. I'm sure we can figure something >> out. >> >> rbr >> >> On Nov 18, 9:12 am, Cerebrus <[email protected]> wrote: >> > Possibly something like : >> > >> > --- >> > SELECT MAX(SUBSTRING(Student_ID, 5, 4)) >> > FROM Student_Details >> > WHERE SUBSTRING(Student_ID, 2, 2) = @TwoDigitYear >> > --- >> > >> > Example param value: @TwoDigitYear = '09' >> > >> > This would give you something like '0118'. You then know that the next >> > registration number for that year should be '0119' (You could also >> > return the value incremented by one in SQL itself.) >> > >> > On Nov 18, 6:36 pm, nu1silva <[email protected]> wrote: >> > >> > >> > >> > > ok here we go again.... :( >> > >> > > asked this before an got some great answers but i'm still not able to >> > > get it right :( >> > >> > > first of all, >> > > im doin the project on Visual Studio 2005 C#.NET and using MSSQL >> > > 2005. >> > >> > > the objective is to register a student and save data on the DB. >> > >> > > what i want to do is create a custom Registration number or the >> > > students that register for a course >> > > examples, >> > > T(year)-(student number) >> > > so itll be like >>> T09-0118, T09-0001, T09-1234 >> > >> > > how to get the input>> >> > >> > > T >> we must ad that >> > > 09 >> should get from date >> > > - >> we add >> > > 0001 >> (where can i get that???) >> > >> > > and it would be great if i can create the Registration Number before i >> > > insert it to the database. >> > >> > > im still learning and would appreciate the help :) >> > > Thanks- Hide quoted text - >> > >> > - Show quoted text - >> > > -- Thank You, Best Regards, Nuwan S. Silva 170, St. Anthony's Road, Moratumulla, Moratuwa, Sri Lanka. Mobile : +94 779 804 543 email : [email protected]
