DECLARE @RowCount INT
SET @RowCount = 999
SELECT RIGHT('00' + CAST(@RowCount AS varchar(3)), 3) -- Prints 999
SET @RowCount = 1000
SELECT RIGHT('00' + CAST(@RowCount AS varchar(3)), 3) -- Prints 00*
On Fri, Nov 20, 2009 at 6:33 PM, Nuwan Silva <[email protected]> wrote:
>
> 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]
>
