"Sham Yemul" <yemu***@yahoo.com> wrote in message
news:%23%23GW6XSvGHA.3264@TK2MSFTNGP03.phx.gbl...
> Hello ,
>
> I am new to SQL server news group, not sure its a right place for my
> question.
>
> We need a custom sequencing solution for Patient's OPD no, the number has
> two parts Unique Number in financial year and year tag for e.g. 1200/06
> where 1200 is unique number in the financial year 2006.
> We created two fields one to store Unique number of financial year and
> other
> to store year tag.
>
> I wrote a function on SQL server 2000 that returns Next number available
> in
> the table. But I was surprised to know that when used in multi-user
> con-current environment the OPD No is getting repeated, which is not
> acceptable.
>
> CREATE FUNCTION [dbo].[AutoNum] (@YearTag VARCHAR(12))
> RETURNS BIGINT  AS
> BEGIN
> RETURN (SELECT MAX(OPDNo) FROM tblOpdNo WHERE YearTag=@YearTag)
> END
>
> After some searching, I came to know that I need to do some table locking
> before getting new number, for this I think we need some time wasting
> logic
> if table is locked by other user, otherwise it will give a fail result.
>
> I need help or some sample if someone has already faced similar problem.
>
> Thanks in advance.
> Regards
> [Sham]
>
>

"Roger Wolter[MSFT]" <rwol***@online.microsoft.com> wrote in message
news:ubVq9dvvGHA.4972@TK2MSFTNGP05.phx.gbl...
> The simplest thing to do would be to make this column an IDENTITY column
so
> that SQL Server automatically increments the number for you.
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
>
> "Sham Yemul" <yemu***@yahoo.com> wrote in message
> news:%23%23GW6XSvGHA.3264@TK2MSFTNGP03.phx.gbl...
> > Hello ,
> >
> > I am new to SQL server news group, not sure its a right place for my
> > question.
> >
> > We need a custom sequencing solution for Patient's OPD no, the number
has
> > two parts Unique Number in financial year and year tag for e.g. 1200/06
> > where 1200 is unique number in the financial year 2006.
> > We created two fields one to store Unique number of financial year and
> > other
> > to store year tag.
> >
> > I wrote a function on SQL server 2000 that returns Next number available
> > in
> > the table. But I was surprised to know that when used in multi-user
> > con-current environment the OPD No is getting repeated, which is not
> > acceptable.
> >
> > CREATE FUNCTION [dbo].[AutoNum] (@YearTag VARCHAR(12))
> > RETURNS BIGINT  AS
> > BEGIN
> > RETURN (SELECT MAX(OPDNo) FROM tblOpdNo WHERE YearTag=@YearTag)
> > END
> >
> > After some searching, I came to know that I need to do some table
locking
> > before getting new number, for this I think we need some time wasting
> > logic
> > if table is locked by other user, otherwise it will give a fail result.
> >
> > I need help or some sample if someone has already faced similar problem.
> >
> > Thanks in advance.
> > Regards
> > [Sham]
> >
> >
>
>

"Sham Yemul" <yemu***@yahoo.com> wrote in message
news:uQ0DuI3vGHA.560@TK2MSFTNGP05.phx.gbl...
> Hello Roger,
>
> Thanks for the suggestion.
>
> 1) The field may have repeated values (carried over entries from last
> year),
> so I can not make it Identity to my knowledge,if you know any alternative
> let me know.
> Some-one suggested making it Unique Index so that it won't repeat, but I
> can
> not put such index on this field since it may have repeated values.
> 2) I do not want gaps , some ppl expereinced that using Identity may
> create
> gaps, which is not allowed by Accounts/auditor.
> 3) Anyhow it seems I need a custom routine for this. I am trying to
> develop
> one, but facing problem when locking the table.
> Also if the table is locked and if another user tries to get new number it
> should give some error, or there should be some time wasting logic to try
> again after few moments to get the table and next number.
>
> Regards
> [Sham]
> "Roger Wolter[MSFT]" <rwol***@online.microsoft.com> wrote in message
> news:ubVq9dvvGHA.4972@TK2MSFTNGP05.phx.gbl...
>> The simplest thing to do would be to make this column an IDENTITY column
> so
>> that SQL Server automatically increments the number for you.
>>
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>>
>> "Sham Yemul" <yemu***@yahoo.com> wrote in message
>> news:%23%23GW6XSvGHA.3264@TK2MSFTNGP03.phx.gbl...
>> > Hello ,
>> >
>> > I am new to SQL server news group, not sure its a right place for my
>> > question.
>> >
>> > We need a custom sequencing solution for Patient's OPD no, the number
> has
>> > two parts Unique Number in financial year and year tag for e.g. 1200/06
>> > where 1200 is unique number in the financial year 2006.
>> > We created two fields one to store Unique number of financial year and
>> > other
>> > to store year tag.
>> >
>> > I wrote a function on SQL server 2000 that returns Next number
>> > available
>> > in
>> > the table. But I was surprised to know that when used in multi-user
>> > con-current environment the OPD No is getting repeated, which is not
>> > acceptable.
>> >
>> > CREATE FUNCTION [dbo].[AutoNum] (@YearTag VARCHAR(12))
>> > RETURNS BIGINT  AS
>> > BEGIN
>> > RETURN (SELECT MAX(OPDNo) FROM tblOpdNo WHERE YearTag=@YearTag)
>> > END
>> >
>> > After some searching, I came to know that I need to do some table
> locking
>> > before getting new number, for this I think we need some time wasting
>> > logic
>> > if table is locked by other user, otherwise it will give a fail result.
>> >
>> > I need help or some sample if someone has already faced similar
>> > problem.
>> >
>> > Thanks in advance.
>> > Regards
>> > [Sham]
>> >
>> >
>>
>>
>
>