Como generar el numero de nuestros comprobantes sin morir en el intento

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