En la mayoria de nuestras aplicaciones tenemos la necesidad de poder generar el numero de nuestros comprobantes (Ordenes de compra, pedidos de ventas, etc)
Existen diferentes tecnicas para poder realizar esta operacion. En los ejemplos de este post mostrate las dos formas mas tradicionales de hacerlo y cuales son sus pros y contras de cada una.
Para estos ejemplos crearemos una base de datos nueva con la siguiente sentencia
CREATE
DATABASE EJEMPLO_NUMERACION
Usando autonumericos
Dentro de SQLserver podemos definir en un tipo de dato entero la posibilidad de que sea autoincremental, esto hara que de forma automatica el motor de base de datos calcule el valor que debe ir en ese campo. Usando esta posibilidad podriamos definir que en nuestros comprobantes el campo de numero sea autonumerico.
El siguiente ejemplo ilustra como hacer esta operacion
USE
EJEMPLO_NUMERACION
GO
CREATE
TABLE OC
(ID
INT IDENTITY, FECHA
DATETIME NOT NULL,
CLIENTE VARCHAR(50) NOT NULL)
GO
Como se podra observer el campo ID es de tipo int y a su vez tiene la propiedad de ser autoincremental, el resto de los campos corresponden a datos necesarios de la Orden de compra de este ejemplo.
Si queremos insertar un nuevo registro (o sea una nueva orden de compra) simplemente hacemos lo siguiente
INSERT
INTO OC
(FECHA
,CLIENTE
)
VALUES
(GETDATE(),'IBM')
go
Si luego de esto hacemos un select a la tabla OC y vemos los registros podremos observar que se ha generado de forma automatica el ID 1, si hacemos otro insert valido veremos que se generara el 2 y asi sucesivamente.
Ahora bien, que sucede si por ejemplo hacemos un insert y no se pudo realizar la transaccion, por ejemplo queremos insertar una OC donde el campo Cliente sea nulo lo cual no esta permitido por la integridad de la base de datos.
INSERT
INTO OC
(FECHA
,CLIENTE
)
VALUES
(GETDATE(),NULL)
go
Al hacer esta instruccion recibiremos el siguiente error:
Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'CLIENTE', table 'EJEMPLO_NUMERACION.dbo.OC'; column does not allow nulls. INSERT fails.
Bien, el registro si hacemos un select a la tabla no se ha generado y eso es correcto ya que ha existido un problema.
Ahora bien, hasta el momento solo tenemos generada la OC con ID = 1 y la 2 ha fallado. Pues arreglamos el tema del null en el insert para que se genere la OC = 2 ya que es la que continua luego de la 1.
INSERT
INTO OC (FECHA,CLIENTE)
VALUES
(GETDATE(),'MICROSOFT')
go
El registro se genero ahora "correctamente", pero si hacemos un select a la tabla nos encontraremos que ahora tenemos el ID = 1 y el ID = 3, con lo cual estamos teniendo huecos en nuestra numeracion.
Como se podra observar es un grave problema tener estos huecos en los numeros de los comprobantes (imagine que fueran facturas por ejemplo), pues esto se debe a como funciona el autonumero de SQLServer, es simple de usar e implementar pero hay que saber lo de las transacciones, ante una falla el numero lo reservo igual y considero que se genero con lo cual lo salta 
Usando tabla numeradora
Otra alternativa a la que vimos es la de generar una tabla donde tendremos los numeros de los comprobantes y usarla para numerar uno nuevo, si la transaccion fallo simplemente hay que tener el recaudo de no cambiar el valor en la tabla numeradora.
Entonces, lo primero que generamos es esta tabla numeradora
CREATE
TABLE NUMERADOR (TIPO_COMPROBANTE VARCHAR(100),
ULTIMO_NUMERO INT)
GO
INSERT
INTO NUMERADOR
VALUES ('OC',1
)
INSERT
INTO NUMERADOR
VALUES ('FACTURAS',10
)
GO
La tabla numeradora podra ser utilizada por distintos comprobantes, de ahi es que tiene un campo denominado tipo_comprobante y otro ultimo_numero.
Como tambien se puede observar se le han insertado 2 registros, uno para los comprobantes de Ordendes de Compra y el otro para facturas.
Ahora bien, lo que debemos hacer como siguiente paso es utilizar esta tabla numeradora para obtener el ultimo_numero sumarle 1 y asignarlo a la OC que estamos generando. Aqui entran a jugar una serie mas de detalles a conocer, si hacemos un Select (sin cambiar el nivel de bloqueo) y otro usuario en otro sitio hace lo mismo podran obtener el mismo ultimo_numero lo cual al sumarle 1 los dos intentaran hacer la misma OC.
Para evitar este tipo de situaciones, ya que no solo necesito numerar sino tambien asegurar que no exista la posibilidad de obtener numeros duplicados (la misma OC por ejemplo) es que vamos a hacer uso de algunos trucos de bloqueos y variables.
DROP
TABLE OC
GO
CREATE
TABLE OC (NUMERO INT, FECHA DATETIME,CLIENTE VARCHAR(50) NOT NULL)
GO
declare @proximo_numero
int begin try
begin
tran
update numerador set @proximo_numero = ultimo_numero = ultimo_numero + 1
where tipo_comprobante = 'OC'
INSERT INTO OC VALUES (@proximo_numero,getdate(),'SQLTOTAL')
commit tran
end try
begin
catch
rollback tran -- primero hacemos un rollback
print @@error -- mostramos el error generado
end
catch
GO
La sentencia UPDATE no solo modifica la tabla sino que ademas actualiza la variable que luego utilizamos para generar el comprobante. De esta manera logramos que si hay 2 procesos haciendo esto por el uso de los bloqueos internos de SQL al hacer uso de la instruccion UPDATE, el segundo proceso no podra hacer update hasta que el primero no haya terminado la transaccion (ya sea por commit o rollback tran)
Si hacemos un select sobre OC vamos a observar que se ha generado el id numero 2, ahora bien si intentamos hacer un nuevo insert pero que falla y luego volvemos a insertar uno que no falla no tendremos los huecos en la numeracion como nos ha sucedido con los identity.
Para probar que no se van a generar 2 numeros iguales en tiempos iguales hay que hacer la siguiente prueba con el Script.
1) Habra dos ventanas de management studio o Query Analizer si usa 2000
2) En ambas copie por ejemplo este codigo
declare @proximo_numero int begin try
begin
tran
update numerador set @proximo_numero = ultimo_numero = ultimo_numero + 1
where tipo_comprobante = 'OC'
INSERT INTO OC VALUES (@proximo_numero,getdate(),'SQLTOTAL')
end try
begin
catch
rollback tran -- primero hacemos un rollback
print @@error -- mostramos el error generado
end
catch
3) Ejecute desde la primer ventana el script, luego hagalo desde la segunda. Como podra observar en la segunda ventana se queda esperando y no procesa , esto se debe que hay bloqueos sobre la tabla numeradora y que el segundo update no ha podido ser realizado.
4) En la primer ventana escriba commit tran y ejecute solo eso
Al hacer esto podra observar que la segunda ventana ha terminado.
5) Haga un select sobre OC y vea los resultados 
Bueno con esto termino, espero que les haya sido de utilidad y espero sus comentarios
Posted
Sun, Mar 16 2008 13:45
by
Maxi Accotto