[DUG] MSSQL Question

[Kyley Harris]

Where I am mainly using Sequences, not tied to a specific field is where
many entities in our applicationall use the same number sequence to be
attached a unique reference, There is a single point of entry to type in
that ref# which will open anything relevant, ie an invoice, a statement a
client, a debtor etc. when talking to customers on the phone, it lets the
client access relevant info very quickly. I don't tend to use a database to
its full capacity as a database as oracle and MSSql etc would let you.. its
a hybrid between the middletier OO layer, and the database.

The actual stored proc I've come up with is as follows... I'm just waiting
to find the flaws.. :D i think 99.999% it will work great.. all my primary
keys are GUIDs.

CREATE PROCEDURE [GETGEN] @TableName VarChar(256), @ACount Int

AS

SET NOCOUNT ON
DECLARE @AVALUE Int
BEGIN TRAN

DECLARE @Res TABLE ( ID int )

BEGIN
  UPDATE GEN_IDTABLE with (XLOCK,ROWLOCK)
  SET KEYVALUE = @AVALUE+ at ACOUNT
  OUTPUT deleted.KEYVALUE INTO @RES(ID)
    WHERE KEYNAME = @TableName
  if @@ROWCOUNT = 0
  begin
    SET @AVALUE = 1
    INSERT INTO GEN_IDTABLE (KEYNAME,KEYVALUE) VALUES
(@TableName, at ACount+1);
  end else
  begin
    SELECT @AVALUE=ID from @Res
  end
END

COMMIT TRAN
RETURN @AVALUE



On Mon, Dec 1, 2008 at 9:40 PM, Neven MacEwan <neven at mwk.co.nz> wrote:

> Kyley
>
> my experience is with postgreqsl, it has sequences (I assume from its
> oracleness), which get tied to a column via the serial type.
>
> I'm puzzled as to where you would use a Sequence/Generator in a non-pk
> situation, unless you
> are talking about your 'documentno' column, which as shown can be done
> via a stored proc
>
> I wonder is mssql eventually will allow functions to update tables (or
> code a primative sequence generation)
> which would allow this
>
> Neven
>
> > The main difference is that a Generator is more like an Oracle Sequence,
> > It is table independant, which allows you to use the same sequence
> > accross more than one table,
> > and you can also have more than one per table. I find the identity is
> > only useful if it is to be a primary key, not useful as a data element.
> >
> > On Mon, Dec 1, 2008 at 5:12 PM, Neven MacEwan <neven at mwk.co.nz
> > <mailto:neven at mwk.co.nz>> wrote:
> >
> >     John
> >
> >     Really interbase generators are more akin to the mssql identity
> column
> >     type, the problem with mssql identity columns WAS
> >     with the @@Identity variable which was per connection (and hence
> >     triggers that inserted other identity columns not return the identity
> >     you would expect),  this has been 'fixed' with the
> >     SCOPE_IDENTITY() function
> >
> >     Stangely identity columns and generators behave the same way, ie they
> >     are both not rolled back by a trans
> >
> >     PostgeSQL, uses a combo of both a column type of 'serial', which auto
> >     generates a 'sequence'
> >
> >     Of course I could be spouting BS.
> >
> >     Neven
> >     //
> >     > Whats others opinion on the merits of trggers etc as discussed
> >     here vs the
> >     > Firebird/Interbase method of using generators?
> >     >
> >     > I have often wondered if this is an area where
> >     Firebird/Interbase has a less
> >     > convenient but more transparent mechanism - where you have to fire
> a
> >     > generator to get a new key value before you start putting data
> >     in a new
> >     > record.
> >     >
> >     > And the generator never rolls back even if the transaction does
> >     - avoiding
> >     > the lock problem.  Mostly in
> >     > my experience this is fine, except some times when the ID
> >     numbers (eg batch
> >     > numbers) are supposed to be strictly sequential and then you
> >     have to get the
> >     > next value from a SQL query, and make sure no-one else is
> >     running another of
> >     > the same batch at the same time.  (In those situations the main
> >     thing that
> >     > protects the database is that only one staff member ever runs
> >     this batch
> >     > operation - I bet thats how a lot of databases run in practice)
> >     >
> >     >
> >     > John
> >     >
> >     > _______________________________________________
> >     > NZ Borland Developers Group - Delphi mailing list
> >     > Post: delphi at delphi.org.nz <mailto:delphi at delphi.org.nz>
> >     > Admin: http://delphi.org.nz/mailman/listinfo/delphi
> >     > Unsubscribe: send an email to delphi-request at delphi.org.nz
> >     <mailto:delphi-request at delphi.org.nz> with Subject: unsubscribe
> >     >
> >     >
> >     >
> >
> >     _______________________________________________
> >     NZ Borland Developers Group - Delphi mailing list
> >     Post: delphi at delphi.org.nz <mailto:delphi at delphi.org.nz>
> >     Admin: http://delphi.org.nz/mailman/listinfo/delphi
> >     Unsubscribe: send an email to delphi-request at delphi.org.nz
> >     <mailto:delphi-request at delphi.org.nz> with Subject: unsubscribe
> >
> >
> >
> >
> > --
> > Kyley Harris
> > Harris Software
> > +64-21-671-821
> > ------------------------------------------------------------------------
> >
> > _______________________________________________
> > NZ Borland Developers Group - Delphi mailing list
> > Post: delphi at delphi.org.nz
> > Admin: http://delphi.org.nz/mailman/listinfo/delphi
> > Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject:
> unsubscribe
>
> _______________________________________________
> NZ Borland Developers Group - Delphi mailing list
> Post: delphi at delphi.org.nz
> Admin: http://delphi.org.nz/mailman/listinfo/delphi
> Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject:
> unsubscribe
>



-- 
Kyley Harris
Harris Software
+64-21-671-821
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://listserver.123.net.nz/pipermail/delphi/attachments/20081201/161da8fa/attachment.html