Selecting Unique Record

[Larry]

I have a stored procedure (below), that is suppose
to get a Reg Number from a table, (Reg_Number), in
such a way that every time the stored procedure is called,
it will get a different reg number, even if the stored
procedure is called simultaneously from two different
places,

However it is not working that way.

If two different users access a function in there
VB program at the same time, the two different users
will get the same reg number.

I have looked at the stored procedure, it looks foolproof,
yet it is not working that way.

Thanks in Advance,

Laurence Nuttall
Programmer Analyst III
UCLA - Division of Continuing Education

'---------------------------------------------------------------------------




Here it is:

CREATE PROCEDURE sp_GetNextRegNu m
@newRegNum char(6) = NULL OUTPUT
AS
LABEL_GET_ANOTH ER_REG:

Select @newRegNum =(select min(Reg) from reg_number)

IF Exists (select Reg from reg_number where reg = @newRegNum )

Begin
Delete from reg_number where reg = @newRegNum

IF @@Error <> 0
Begin
Goto LABEL_GET_ANOTH ER_REG
End
--Endif
End

ELSE
GoTo LABEL_GET_ANOTH ER_REG
--Endif
GO

[Mischa Sandberg]

Re: Selecting Unique Record

Create a dummy table with an identity column and a non-identity column; have
the sproc insert a dummy row; have the sproc return SCOPE_IDENTITY( )

"Larry" <Bliff@Bliff.co m> wrote in message
news:cbvobo$q8i $1@daisy.noc.uc la.edu...[color=blue]
> I have a stored procedure (below), that is suppose
> to get a Reg Number from a table, (Reg_Number), in
> such a way that every time the stored procedure is called,
> it will get a different reg number, even if the stored
> procedure is called simultaneously from two different
> places,
>
> However it is not working that way.
>
> If two different users access a function in there
> VB program at the same time, the two different users
> will get the same reg number.
>
> I have looked at the stored procedure, it looks foolproof,
> yet it is not working that way.
>
> Thanks in Advance,
>
> Laurence Nuttall
> Programmer Analyst III
> UCLA - Division of Continuing Education
>
>[/color]
'---------------------------------------------------------------------------[color=blue]
>
>
>
>
> Here it is:
>
> CREATE PROCEDURE sp_GetNextRegNu m
> @newRegNum char(6) = NULL OUTPUT
> AS
> LABEL_GET_ANOTH ER_REG:
>
> Select @newRegNum =(select min(Reg) from reg_number)
>
> IF Exists (select Reg from reg_number where reg = @newRegNum )
>
> Begin
> Delete from reg_number where reg = @newRegNum
>
> IF @@Error <> 0
> Begin
> Goto LABEL_GET_ANOTH ER_REG
> End
> --Endif
> End
>
> ELSE
> GoTo LABEL_GET_ANOTH ER_REG
> --Endif
> GO[/color]

[Joe Celko]

Re: Selecting Unique Record

The trick is to use a transaction and a serializable isolation level.

That is the first code I have seen with GOTO statements in over 15
years! Did you mean to put a "sp_" prefix on the code?

Could I suggest that instead of deleting the reg numbers as they are
issued, that you update the table to show the date and time they are
issued? No sense losing information and the ability to re-construct a
history. Might want to add the user info, too. Something like this:

CREATE PROCEDURE GetNextRegnum
@newregnum CHAR(6) OUTPUT
AS
BEGIN TRANS
SET @newregnum
=(SELECT MIN(reg)
FROM RegNumbers
WHERE issue_date IS NULL);
UPDATE RegNumbers
SET issue_date = CURRENT_TIMESTA MP
WHERE reg = @newregnum;
<< error handling, commit, rollback here >>
END;

--CELKO--
=============== ============
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Larry]

Re: Selecting Unique Record

Joe,

The stored procedure was written years ago by someone
who is long gone. I inherited it.

For new stored procedures we now know to prefix them with
something other than "sp_"

I know what a transaction is, I am trying to understand what
"serializab le isolation level" is.

Someone suggested that I do this:

begin tran
Select @newRegNum = (Select min(Reg) from reg_number with
updlock,rowlock )

I am not sure about how to configure the transaction,
As this stored procedure is being called from another
stored procedure, at a point in the calling stored procedure
when another transaction is active.

Thank You,

Laurence Nuttall
Programmer Analyst III
UCLA - Division of Continuing Education

'-------------------------------

Here is the DDL for the table:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Reg_Number]') and OBJECTPROPERTY( id, N'IsUserTable') = 1)
drop table [dbo].[Reg_Number]
GO

CREATE TABLE [dbo].[Reg_Number] (
[Reg_Number_ID] [int] NOT NULL ,
[Reg] [char] (6) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Login] [char] (8) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[Modified_date] [smalldatetime] NULL ,
[Concurrent_use] [tinyint] NULL
) ON [PRIMARY]
GO

'---------------------------------------------------------------------------------------------------------------

Joe Celko wrote:[color=blue]
> The trick is to use a transaction and a serializable isolation level.
>
> That is the first code I have seen with GOTO statements in over 15
> years! Did you mean to put a "sp_" prefix on the code?
>
> Could I suggest that instead of deleting the reg numbers as they are
> issued, that you update the table to show the date and time they are
> issued? No sense losing information and the ability to re-construct a
> history. Might want to add the user info, too. Something like this:
>
> CREATE PROCEDURE GetNextRegnum
> @newregnum CHAR(6) OUTPUT
> AS
> BEGIN TRANS
> SET @newregnum
> =(SELECT MIN(reg)
> FROM RegNumbers
> WHERE issue_date IS NULL);
> UPDATE RegNumbers
> SET issue_date = CURRENT_TIMESTA MP
> WHERE reg = @newregnum;
> << error handling, commit, rollback here >>
> END;
>
> --CELKO--
> =============== ============
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Larry]

Re: Selecting Unique Record

A fellow worker just pointed at that:

The @@Error will be zero, even if the
delete could not find a record to delete,

That is:

Delete from reg_number where reg = @newRegNum
IF @@Error <> 0

The if statment will never be true, that is
the @@ERROR will never be non zero.

Is this true?, if so then this stored procedure
never worked at all.

Thanks in Advance,

Larry

Joe Celko wrote:
[color=blue]
> The trick is to use a transaction and a serializable isolation level.
>
> That is the first code I have seen with GOTO statements in over 15
> years! Did you mean to put a "sp_" prefix on the code?
>
> Could I suggest that instead of deleting the reg numbers as they are
> issued, that you update the table to show the date and time they are
> issued? No sense losing information and the ability to re-construct a
> history. Might want to add the user info, too. Something like this:
>
> CREATE PROCEDURE GetNextRegnum
> @newregnum CHAR(6) OUTPUT
> AS
> BEGIN TRANS
> SET @newregnum
> =(SELECT MIN(reg)
> FROM RegNumbers
> WHERE issue_date IS NULL);
> UPDATE RegNumbers
> SET issue_date = CURRENT_TIMESTA MP
> WHERE reg = @newregnum;
> << error handling, commit, rollback here >>
> END;
>
> --CELKO--
> =============== ============
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are.
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Erland Sommarskog]

Re: Selecting Unique Record

Joe Celko (jcelko212@eart hlink.net) writes:[color=blue]
> The trick is to use a transaction and a serializable isolation level.[/color]

But since the default on SQL Server is READ COMMITTED, you must specify
that you need serializable. Thus you suggestion does not fly:
[color=blue]
> CREATE PROCEDURE GetNextRegnum
> @newregnum CHAR(6) OUTPUT
> AS
> BEGIN TRANS
> SET @newregnum
> =(SELECT MIN(reg)
> FROM RegNumbers
> WHERE issue_date IS NULL);
> UPDATE RegNumbers
> SET issue_date = CURRENT_TIMESTA MP
> WHERE reg = @newregnum;
> << error handling, commit, rollback here >>
> END;[/color]

You must say SET TRANSACTION ISOLATION LEVEL SERIALIZABLE first, or else
two simultaneous callers can get the same @newregnum. Using UPDLOCK as
Larry said in his later posting is also good.

Note that whatever the method, if there are two parallel calls, then
the second caller will be blocked until the first commits this transaction.
(Larry said that this procedure is part of a outside transaction.)
[color=blue]
> Could I suggest that instead of deleting the reg numbers as they are
> issued, that you update the table to show the date and time they are
> issued? No sense losing information and the ability to re-construct a
> history. Might want to add the user info, too. Something like this:[/color]

Indeed a good suggestion. Just make sure that there is an index on
(index_date, reg).
[color=blue]
> That is the first code I have seen with GOTO statements in over 15
> years![/color]

Hey! I use GOTO in my code occasionally. (Mainly for error exits.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: Selecting Unique Record

Larry (Bliff@Bliff.co m) writes:[color=blue]
> A fellow worker just pointed at that:
>
> The @@Error will be zero, even if the
> delete could not find a record to delete,[/color]

Yes. Not find a matching row is not an error, but a perfectly normal thing.
[color=blue]
> That is:
>
> Delete from reg_number where reg = @newRegNum
> IF @@Error <> 0
>
> The if statment will never be true, that is
> the @@ERROR will never be non zero.[/color]

Well, @@error could be non-zero for other reasons, for instance a
constraint violation, or a lock timeout.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp