Generar numero correlativo o serial

Bien, poniendo todos los requerimientos de transacciones las y sugerencias
de Mariano el procedimiento quedaria asi:

create procedure
nextval @sqname char(128), ), @retval int OUTPUT
as
IF NOT EXIST( SELECT * FROM generatepk WHERE gpk_pk = @sqname)
RAISERROR 'No existe una secuencia para la clave: ' + sqname , 16, 1
WITH SETERROR

BEGIN TRANSACTION NEXTVALTRX

UPDATE generatepk
SET
@retval = gpk_currentnumber = gpk_currentnumber + 1
WHERE
gpk_pk = @sqname

IF @@ERROR = 0
COMMIT TRAN NEXTVALTRX
ELSE
ROLLBACK TRAN NEXTVALTRX

RETURN

Al utilizar transacciones con nombres puedes incluso anidar sin problemas
transacciones, es decir, este procedimiento puede estar incluido en una
transaccion de mayor nivel. Es mas, deberia estar SIEMPRE en una transaccion
de mayor nivel ya que en el caso de que lo invoques, obtengas un nuevo
numero y luego por algun error (nunca se sabe!!) deshaces la operacion o la
misma queda sin efecto te quedara un agujero en la secuencia de numeros. Por
ejemplo en mi pais (argentina) si estoy numerando facturas es inadmisible
que me queden agujeros en la numeracion..

Ahora no entiendo el porque te esta devolviendo NULL en la variable, podias
postear la parte del codigo de la invocacion mas la creacion del
procedimiento almacenado?.

Saludos
Adrian D. Garcia
MCSD
NDSoft Consultoria y Desarrollo

"news.microsoft.com" wrote in message
news:

Gracias a ambos por su contestacion, he probado ambos ejemplos si buen
suceso, el valor que retorna en ambos es null, no entiendo porque si

ejecuto

el select fuera del procedimiento funciona bien.

Aun estoy tratando de figurar la manera de hacer que el procedimiento en

si

mismo sea una transaccion y asi evitar problemas de bloqueo o accesso
recurrente pero lastimosamente no logro figurar como hacer que mssql
seleccione el registro lo bloquee, permita modificarlo, lo desbloquee y
retorne el valor.

Existe algo en TSQL similar a SELECT FOR UPDATE?, tienen algun ejemplo
similar algo asi como cuando actualizan saldos de clientes o cuentas de
bancos como se hace en para asegurar que dos sessiones no accessen al

mismo

tiempo el mismo registro.

De nuevo, gracias por anticipado.

Josué Maldonado


From: "Adrian Garcia"
Newsgroups: microsoft.public.es.sqlserver
Sent: Sunday, February 01, 2004 9:24 PM
Subject: Re: Generar numero correlativo o serial


> hmm... creo que esto sería asi...
>
> create procedure
> nextval @sqname char(128), ), @retval int OUTPUT
> as
> UPDATE generatepk
> SET
> gpk_currentnumber = gpk_currentnumber + 1
> WHERE
> gpk_pk = @sqname
>
> select @retval = gpk_currentnumber
> from generatepk
> where gpk_pk = @sqname
>
> return
>
> Desde ya para que esto funcione en forma correcta debe existir una
> transaccion ANTES de la invocacion a este procedmiento almacenado.
> Del porque sumamos primero y luego obtenemos? De esta forma nos

aseguramos

> de que estamos bloqueando la fila en forma exclusiva antes de leer el
valor.
> Si no lo hacemos asi podemos estar leyendo los mismos valores si 2
> transacciones acceden al mismo momento al procedimiento almacenado para

la

> misma fila!!!.
>
> Ahora que lo pienso a este procedimiento le esta faltando control de
> errores! que pasa si lo invoco con un valor en sqname que no existe en

la

> tabla?. Uno siempre piensa que este tipo de situaciones nunca pasan pero
la
> realidad nos demuestra que SIEMPRE PASAN! y este tipo de error es uno de
los
> mas dificiles de detectar cuando probamos o ponemos en produccion a
nuestros
> desarrollos. Yo soy de los que piensan que hay que cuidarse en salud,

asi

> que aqui esta el codigo modificado...
>
> create procedure nextval
> @sqname char(128), ), @retval int OUTPUT
> as
> IF NOT EXIST( SELECT * FROM generatepk WHERE gpk_pk = @sqname)
> RAISERROR 'No existe una secuencia para la clave: ' + sqname , 16, 1
> WITH SETERROR
>
> UPDATE generatepk
> SET
> gpk_currentnumber = gpk_currentnumber + 1
> WHERE
> gpk_pk = @sqname
>
> select @retval = gpk_currentnumber
> from generatepk
> where gpk_pk = @sqname
>
> return
>
> Desde ya una mejor opcion (para algunos discutible) de insertar el

mensage

> de error en la tabla sysmessage e invocar directamente al RAISERROR con
esta
> opcion.
>
> Para invocar a este procedimiento almacenado deberias utilizar algo
parecido
> a esto:
>
> BEGIN TRAN
> DECLARE @numero INT
> EXEC nextval 'SQEMPLEADOS' , @numero
> IF @@ERROR <> 0
> BEGIN
> ... tratamos aqui al error
> END
>
> ...
>
> COMMIT TRAN
> Saludos
> Adrian D. Garcia
> MCSD
> NDSoft Consultoria y Desarrollo
>
> "news.microsoft.com" wrote in message
> news:
> > Hola lista,
> >
> > Soy nuevo en T-SQL, asi que antes que nada quiero pedir disculpas
> > si esta inquietud resulta ofensiva para alguien.
> >
> > Tengo una tabla para generar numeros correlativos de la forma:
> >
> > pkname pkvalue
> > SQNOFACT 4566
> > SQNOEMP 981
> >
> > Quisiera definir un procedimiento almacenado que lea los valores de la
> > tabla, de acuerdo al parametro de pkname pasado al procedimiento y

luego

> > retorne el valor de la columna pkvalue y despues incremente en 1 el
valor
> > esa columna. Este procedimiento se debe ejecutar como una transaccion
para
> > evitar errores si hay accesos recurrentes al mismo registro.
> >
> > Siguiendo la ayuda he definido un pequeño borrador pero no logro que
> > funcione correctamente:
> >
> > create procedure
> > nextval @sqname char(128), @retval int OUTPUT
> > as
> >
> > select @retval = gpk_currentnumber
> > from
> > generatepk where gpk_pk = @sqname
> >
> >
> > return @retval
> >
> > pero al ejecutarlo asi:
> > execute nextval SQEMPLEADOS,1
> >
> > No devuelve ningun dato pero tampoco genera ningun error, agradecere
mucho
> > su colaboracion.
> >
> > Sinceramente,
> >
> > Josue Maldonado.
> >
> >
> >
>
>

"Adrian Garcia" wrote in message
news:
> hmm... creo que esto sería asi...
>
> create procedure
> nextval @sqname char(128), ), @retval int OUTPUT
> as
> UPDATE generatepk
> SET
> gpk_currentnumber = gpk_currentnumber + 1
> WHERE
> gpk_pk = @sqname
>
> select @retval = gpk_currentnumber
> from generatepk
> where gpk_pk = @sqname
>
> return
>
> Desde ya para que esto funcione en forma correcta debe existir una
> transaccion ANTES de la invocacion a este procedmiento almacenado.
> Del porque sumamos primero y luego obtenemos? De esta forma nos

aseguramos

> de que estamos bloqueando la fila en forma exclusiva antes de leer el
valor.
> Si no lo hacemos asi podemos estar leyendo los mismos valores si 2
> transacciones acceden al mismo momento al procedimiento almacenado para

la

> misma fila!!!.
>
> Ahora que lo pienso a este procedimiento le esta faltando control de
> errores! que pasa si lo invoco con un valor en sqname que no existe en

la

> tabla?. Uno siempre piensa que este tipo de situaciones nunca pasan pero
la
> realidad nos demuestra que SIEMPRE PASAN! y este tipo de error es uno de
los
> mas dificiles de detectar cuando probamos o ponemos en produccion a
nuestros
> desarrollos. Yo soy de los que piensan que hay que cuidarse en salud,

asi

> que aqui esta el codigo modificado...
>
> create procedure nextval
> @sqname char(128), ), @retval int OUTPUT
> as
> IF NOT EXIST( SELECT * FROM generatepk WHERE gpk_pk = @sqname)
> RAISERROR 'No existe una secuencia para la clave: ' + sqname , 16, 1
> WITH SETERROR
>
> UPDATE generatepk
> SET
> gpk_currentnumber = gpk_currentnumber + 1
> WHERE
> gpk_pk = @sqname
>
> select @retval = gpk_currentnumber
> from generatepk
> where gpk_pk = @sqname
>
> return
>
> Desde ya una mejor opcion (para algunos discutible) de insertar el

mensage

> de error en la tabla sysmessage e invocar directamente al RAISERROR con
esta
> opcion.
>
> Para invocar a este procedimiento almacenado deberias utilizar algo
parecido
> a esto:
>
> BEGIN TRAN
> DECLARE @numero INT
> EXEC nextval 'SQEMPLEADOS' , @numero
> IF @@ERROR <> 0
> BEGIN
> ... tratamos aqui al error
> END
>
> ...
>
> COMMIT TRAN
> Saludos
> Adrian D. Garcia
> MCSD
> NDSoft Consultoria y Desarrollo
>
> "news.microsoft.com" wrote in message
> news:
> > Hola lista,
> >
> > Soy nuevo en T-SQL, asi que antes que nada quiero pedir disculpas
> > si esta inquietud resulta ofensiva para alguien.
> >
> > Tengo una tabla para generar numeros correlativos de la forma:
> >
> > pkname pkvalue
> > SQNOFACT 4566
> > SQNOEMP 981
> >
> > Quisiera definir un procedimiento almacenado que lea los valores de la
> > tabla, de acuerdo al parametro de pkname pasado al procedimiento y

luego

> > retorne el valor de la columna pkvalue y despues incremente en 1 el
valor
> > esa columna. Este procedimiento se debe ejecutar como una transaccion
para
> > evitar errores si hay accesos recurrentes al mismo registro.
> >
> > Siguiendo la ayuda he definido un pequeño borrador pero no logro que
> > funcione correctamente:
> >
> > create procedure
> > nextval @sqname char(128), @retval int OUTPUT
> > as
> >
> > select @retval = gpk_currentnumber
> > from
> > generatepk where gpk_pk = @sqname
> >
> >
> > return @retval
> >
> > pero al ejecutarlo asi:
> > execute nextval SQEMPLEADOS,1
> >
> > No devuelve ningun dato pero tampoco genera ningun error, agradecere
mucho
> > su colaboracion.
> >
> > Sinceramente,
> >
> > Josue Maldonado.
> >
> >
> >
>
>

Adrian,

Debo mencionar y lo debi haber hecho desde el primer mensaje que uso
MSDE 2000, actualmente estoy bajando el sp3. No se si eso tenga algo que
ver con mi problema pero el codigo que enviastes abajo genera un error
al ejecutarlo en el servidor.

Primero genero este error:
Line 2: Incorrect syntax near ')'
Cambie esta linea:
nextval @sqname char(128), ), @retval int OUTPUT
por:
nextval @sqname char(128), @retval int OUTPUT

Luego obtuve el siguiente error:
Incorrect syntax near the keyword 'SELECT'

Es posible que haya algo mas que no este bien o tendra algo que ver que
sea MSDE y no MSSQL

El ultimo codigo de la definicion del procedimiento es este:

create procedure
nextval @sqname char(128), @retval int OUTPUT
as

BEGIN TRANSACTION NEXTVALTRX

UPDATE generatepk
SET
@retval = gpk_currentnumber = gpk_currentnumber + 1
WHERE
gpk_pk = @sqname

IF @@ERROR = 0
COMMIT TRAN NEXTVALTRX
ELSE
ROLLBACK TRAN NEXTVALTRX

RETURN

La invocacion o ejecucion la hago asi:
DECLARE @numero INT
EXEC nextval 'SQEMPLEADOS' , @numero
select @numero


Sinceramente,

Josué Maldonado.

Adrian D. Garcia wrote:

Bien, poniendo todos los requerimientos de transacciones las y sugerencias
de Mariano el procedimiento quedaria asi:

create procedure
nextval @sqname char(128), ), @retval int OUTPUT
as
IF NOT EXIST( SELECT * FROM generatepk WHERE gpk_pk = @sqname)
RAISERROR 'No existe una secuencia para la clave: ' + sqname , 16, 1
WITH SETERROR

BEGIN TRANSACTION NEXTVALTRX

UPDATE generatepk
SET
@retval = gpk_currentnumber = gpk_currentnumber + 1
WHERE
gpk_pk = @sqname

IF @@ERROR = 0
COMMIT TRAN NEXTVALTRX
ELSE
ROLLBACK TRAN NEXTVALTRX

RETURN

Al utilizar transacciones con nombres puedes incluso anidar sin problemas
transacciones, es decir, este procedimiento puede estar incluido en una
transaccion de mayor nivel. Es mas, deberia estar SIEMPRE en una transaccion
de mayor nivel ya que en el caso de que lo invoques, obtengas un nuevo
numero y luego por algun error (nunca se sabe!!) deshaces la operacion o la
misma queda sin efecto te quedara un agujero en la secuencia de numeros. Por
ejemplo en mi pais (argentina) si estoy numerando facturas es inadmisible
que me queden agujeros en la numeracion..

Ahora no entiendo el porque te esta devolviendo NULL en la variable, podias
postear la parte del codigo de la invocacion mas la creacion del
procedimiento almacenado?.

Saludos
Adrian D. Garcia
MCSD
NDSoft Consultoria y Desarrollo

"news.microsoft.com" wrote in message
news:

Gracias a ambos por su contestacion, he probado ambos ejemplos si buen
suceso, el valor que retorna en ambos es null, no entiendo porque si

ejecuto

el select fuera del procedimiento funciona bien.

Aun estoy tratando de figurar la manera de hacer que el procedimiento en

si

mismo sea una transaccion y asi evitar problemas de bloqueo o accesso
recurrente pero lastimosamente no logro figurar como hacer que mssql
seleccione el registro lo bloquee, permita modificarlo, lo desbloquee y
retorne el valor.

Existe algo en TSQL similar a SELECT FOR UPDATE?, tienen algun ejemplo
similar algo asi como cuando actualizan saldos de clientes o cuentas de
bancos como se hace en para asegurar que dos sessiones no accessen al

mismo

tiempo el mismo registro.

De nuevo, gracias por anticipado.

Josué Maldonado


From: "Adrian Garcia"
Newsgroups: microsoft.public.es.sqlserver
Sent: Sunday, February 01, 2004 9:24 PM
Subject: Re: Generar numero correlativo o serial

hmm... creo que esto sería asi...

create procedure
nextval @sqname char(128), ), @retval int OUTPUT
as
UPDATE generatepk
SET
gpk_currentnumber = gpk_currentnumber + 1
WHERE
gpk_pk = @sqname

select @retval = gpk_currentnumber
from generatepk
where gpk_pk = @sqname

return

Desde ya para que esto funcione en forma correcta debe existir una
transaccion ANTES de la invocacion a este procedmiento almacenado.
Del porque sumamos primero y luego obtenemos? De esta forma nos

aseguramos

de que estamos bloqueando la fila en forma exclusiva antes de leer el

valor.

Si no lo hacemos asi podemos estar leyendo los mismos valores si 2
transacciones acceden al mismo momento al procedimiento almacenado para

la

misma fila!!!.

Ahora que lo pienso a este procedimiento le esta faltando control de
errores! que pasa si lo invoco con un valor en sqname que no existe en

la

tabla?. Uno siempre piensa que este tipo de situaciones nunca pasan pero

la

realidad nos demuestra que SIEMPRE PASAN! y este tipo de error es uno de

los

mas dificiles de detectar cuando probamos o ponemos en produccion a

nuestros

desarrollos. Yo soy de los que piensan que hay que cuidarse en salud,

asi

que aqui esta el codigo modificado...

create procedure nextval
@sqname char(128), ), @retval int OUTPUT
as
IF NOT EXIST( SELECT * FROM generatepk WHERE gpk_pk = @sqname)
RAISERROR 'No existe una secuencia para la clave: ' + sqname , 16, 1
WITH SETERROR

UPDATE generatepk
SET
gpk_currentnumber = gpk_currentnumber + 1
WHERE
gpk_pk = @sqname

select @retval = gpk_currentnumber
from generatepk
where gpk_pk = @sqname

return

Desde ya una mejor opcion (para algunos discutible) de insertar el

mensage

de error en la tabla sysmessage e invocar directamente al RAISERROR con

esta

opcion.

Para invocar a este procedimiento almacenado deberias utilizar algo

parecido

a esto:

BEGIN TRAN
DECLARE @numero INT
EXEC nextval 'SQEMPLEADOS' , @numero
IF @@ERROR <> 0
BEGIN
... tratamos aqui al error
END

...

COMMIT TRAN
Saludos
Adrian D. Garcia
MCSD
NDSoft Consultoria y Desarrollo

"news.microsoft.com" wrote in message
news:

Hola lista,

Soy nuevo en T-SQL, asi que antes que nada quiero pedir disculpas
si esta inquietud resulta ofensiva para alguien.

Tengo una tabla para generar numeros correlativos de la forma:

pkname pkvalue
SQNOFACT 4566
SQNOEMP 981

Quisiera definir un procedimiento almacenado que lea los valores de la
tabla, de acuerdo al parametro de pkname pasado al procedimiento y

luego

retorne el valor de la columna pkvalue y despues incremente en 1 el

valor

esa columna. Este procedimiento se debe ejecutar como una transaccion

para

evitar errores si hay accesos recurrentes al mismo registro.

Siguiendo la ayuda he definido un pequeño borrador pero no logro que
funcione correctamente:

create procedure
nextval @sqname char(128), @retval int OUTPUT
as

select @retval = gpk_currentnumber
from
generatepk where gpk_pk = @sqname


return @retval

pero al ejecutarlo asi:
execute nextval SQEMPLEADOS,1

No devuelve ningun dato pero tampoco genera ningun error, agradecere

mucho

su colaboracion.

Sinceramente,

Josue Maldonado.

"Adrian Garcia" wrote in message
news:

hmm... creo que esto sería asi...

create procedure
nextval @sqname char(128), ), @retval int OUTPUT
as
UPDATE generatepk
SET
gpk_currentnumber = gpk_currentnumber + 1
WHERE
gpk_pk = @sqname

select @retval = gpk_currentnumber
from generatepk
where gpk_pk = @sqname

return

Desde ya para que esto funcione en forma correcta debe existir una
transaccion ANTES de la invocacion a este procedmiento almacenado.
Del porque sumamos primero y luego obtenemos? De esta forma nos

aseguramos

de que estamos bloqueando la fila en forma exclusiva antes de leer el

valor.

Si no lo hacemos asi podemos estar leyendo los mismos valores si 2
transacciones acceden al mismo momento al procedimiento almacenado para

la

misma fila!!!.

Ahora que lo pienso a este procedimiento le esta faltando control de
errores! que pasa si lo invoco con un valor en sqname que no existe en

la

tabla?. Uno siempre piensa que este tipo de situaciones nunca pasan pero

la

realidad nos demuestra que SIEMPRE PASAN! y este tipo de error es uno de

los

mas dificiles de detectar cuando probamos o ponemos en produccion a

nuestros

desarrollos. Yo soy de los que piensan que hay que cuidarse en salud,

asi

que aqui esta el codigo modificado...

create procedure nextval
@sqname char(128), ), @retval int OUTPUT
as
IF NOT EXIST( SELECT * FROM generatepk WHERE gpk_pk = @sqname)
RAISERROR 'No existe una secuencia para la clave: ' + sqname , 16, 1
WITH SETERROR

UPDATE generatepk
SET
gpk_currentnumber = gpk_currentnumber + 1
WHERE
gpk_pk = @sqname

select @retval = gpk_currentnumber
from generatepk
where gpk_pk = @sqname

return

Desde ya una mejor opcion (para algunos discutible) de insertar el

mensage

de error en la tabla sysmessage e invocar directamente al RAISERROR con

esta

opcion.

Para invocar a este procedimiento almacenado deberias utilizar algo

parecido

a esto:

BEGIN TRAN
DECLARE @numero INT
EXEC nextval 'SQEMPLEADOS' , @numero
IF @@ERROR <> 0
BEGIN
... tratamos aqui al error
END

...

COMMIT TRAN
Saludos
Adrian D. Garcia
MCSD
NDSoft Consultoria y Desarrollo

"news.microsoft.com" wrote in message
news:

Hola lista,

Soy nuevo en T-SQL, asi que antes que nada quiero pedir disculpas
si esta inquietud resulta ofensiva para alguien.

Tengo una tabla para generar numeros correlativos de la forma:

pkname pkvalue
SQNOFACT 4566
SQNOEMP 981

Quisiera definir un procedimiento almacenado que lea los valores de la
tabla, de acuerdo al parametro de pkname pasado al procedimiento y

luego

retorne el valor de la columna pkvalue y despues incremente en 1 el

valor

esa columna. Este procedimiento se debe ejecutar como una transaccion

para

evitar errores si hay accesos recurrentes al mismo registro.

Siguiendo la ayuda he definido un pequeño borrador pero no logro que
funcione correctamente:

create procedure
nextval @sqname char(128), @retval int OUTPUT
as

select @retval = gpk_currentnumber
from
generatepk where gpk_pk = @sqname


return @retval

pero al ejecutarlo asi:
execute nextval SQEMPLEADOS,1

No devuelve ningun dato pero tampoco genera ningun error, agradecere

mucho

su colaboracion.

Sinceramente,

Josue Maldonado.

Ops!
Tal cual lo habias informado habia varios errores de sintaxis en el codigo
que arme.
Eso pasa por escribir codigo directmante como respuesta en vez de probar. He
aqui el codigo probado y que funciona correctamente:

Para ello arme esta tabla de prueba segun las especificaciones que habias
armado en tu primer post:


CREATE TABLE generatepk
(pkname VARCHAR(10) PRIMARY KEY,
pkvalue INT DEFAULT 0)
GO

Luego le asigne valores

INSERT INTO generatepk VALUES ('SQNOFACT', 4566)
INSERT INTO generatepk VALUES ('SQNOEMP', 981)

Y cree el procedimiento almacenado

CREATE procedure nextval
@sqname char(128),
@retval int OUTPUT
as
IF NOT EXISTS( SELECT * FROM generatepk WHERE pkname = @sqname)
RAISERROR ('No existe una secuencia para la clave %s' , 16, 1 ,
@sqname ) WITH SETERROR

UPDATE generatepk
SET
@retval = pkvalue= pkvalue+ 1
WHERE
pkname = @sqname

RETURN
GO

Como podras ver saque le codigo de transacciones ya que el mismo no es
necesario al realizar el UPDATE y obtener la respuesta todo en una sola
operacion

Luego lainvocacion es la siguiente:

DECLARE @numero INT
EXEC nextval 'SQNOEMP' , @numero OUTPUT
select @numero

Lo que estaba faltando en la invocacion es la palabra reservada OUTPUT en el
parametro de salida.

Saludos
Adrian D. Garcia
MCSD
NDSoft Consultoria y Desarrollo

"Josué Maldonado" <josue|at@|lamundial.hn> wrote in message
news:

Adrian,

Debo mencionar y lo debi haber hecho desde el primer mensaje que uso
MSDE 2000, actualmente estoy bajando el sp3. No se si eso tenga algo que
ver con mi problema pero el codigo que enviastes abajo genera un error
al ejecutarlo en el servidor.

Primero genero este error:
Line 2: Incorrect syntax near ')'
Cambie esta linea:
nextval @sqname char(128), ), @retval int OUTPUT
por:
nextval @sqname char(128), @retval int OUTPUT

Luego obtuve el siguiente error:
Incorrect syntax near the keyword 'SELECT'

Es posible que haya algo mas que no este bien o tendra algo que ver que
sea MSDE y no MSSQL

El ultimo codigo de la definicion del procedimiento es este:

create procedure
nextval @sqname char(128), @retval int OUTPUT
as

BEGIN TRANSACTION NEXTVALTRX

UPDATE generatepk
SET
@retval = gpk_currentnumber = gpk_currentnumber + 1
WHERE
gpk_pk = @sqname

IF @@ERROR = 0
COMMIT TRAN NEXTVALTRX
ELSE
ROLLBACK TRAN NEXTVALTRX

RETURN

La invocacion o ejecucion la hago asi:
DECLARE @numero INT
EXEC nextval 'SQEMPLEADOS' , @numero
select @numero


Sinceramente,

Josué Maldonado.

Adrian D. Garcia wrote:
> Bien, poniendo todos los requerimientos de transacciones las y

sugerencias

> de Mariano el procedimiento quedaria asi:
>
> create procedure
> nextval @sqname char(128), ), @retval int OUTPUT
> as
> IF NOT EXIST( SELECT * FROM generatepk WHERE gpk_pk = @sqname)
> RAISERROR 'No existe una secuencia para la clave: ' + sqname , 16, 1
> WITH SETERROR
>
> BEGIN TRANSACTION NEXTVALTRX
>
> UPDATE generatepk
> SET
> @retval = gpk_currentnumber = gpk_currentnumber + 1
> WHERE
> gpk_pk = @sqname
>
> IF @@ERROR = 0
> COMMIT TRAN NEXTVALTRX
> ELSE
> ROLLBACK TRAN NEXTVALTRX
>
> RETURN
>
> Al utilizar transacciones con nombres puedes incluso anidar sin

problemas

> transacciones, es decir, este procedimiento puede estar incluido en una
> transaccion de mayor nivel. Es mas, deberia estar SIEMPRE en una

transaccion

> de mayor nivel ya que en el caso de que lo invoques, obtengas un nuevo
> numero y luego por algun error (nunca se sabe!!) deshaces la operacion o

la

> misma queda sin efecto te quedara un agujero en la secuencia de numeros.

Por

> ejemplo en mi pais (argentina) si estoy numerando facturas es

inadmisible

> que me queden agujeros en la numeracion..
>
> Ahora no entiendo el porque te esta devolviendo NULL en la variable,

podias

> postear la parte del codigo de la invocacion mas la creacion del
> procedimiento almacenado?.
>
> Saludos
> Adrian D. Garcia
> MCSD
> NDSoft Consultoria y Desarrollo
>
> "news.microsoft.com" wrote in message
> news:
>
>>Gracias a ambos por su contestacion, he probado ambos ejemplos si buen
>>suceso, el valor que retorna en ambos es null, no entiendo porque si
>
> ejecuto
>
>>el select fuera del procedimiento funciona bien.
>>
>>Aun estoy tratando de figurar la manera de hacer que el procedimiento en
>
> si
>
>>mismo sea una transaccion y asi evitar problemas de bloqueo o accesso
>>recurrente pero lastimosamente no logro figurar como hacer que mssql
>>seleccione el registro lo bloquee, permita modificarlo, lo desbloquee y
>>retorne el valor.
>>
>>Existe algo en TSQL similar a SELECT FOR UPDATE?, tienen algun ejemplo
>>similar algo asi como cuando actualizan saldos de clientes o cuentas de
>>bancos como se hace en para asegurar que dos sessiones no accessen al
>
> mismo
>
>>tiempo el mismo registro.
>>
>>De nuevo, gracias por anticipado.
>>
>>Josué Maldonado
>>
>>
>>From: "Adrian Garcia"
>>Newsgroups: microsoft.public.es.sqlserver
>>Sent: Sunday, February 01, 2004 9:24 PM
>>Subject: Re: Generar numero correlativo o serial
>>
>>
>>
>>>hmm... creo que esto sería asi...
>>>
>>>create procedure
>>> nextval @sqname char(128), ), @retval int OUTPUT
>>>as
>>>UPDATE generatepk
>>>SET
>>> gpk_currentnumber = gpk_currentnumber + 1
>>>WHERE
>>> gpk_pk = @sqname
>>>
>>> select @retval = gpk_currentnumber
>>> from generatepk
>>> where gpk_pk = @sqname
>>>
>>> return
>>>
>>>Desde ya para que esto funcione en forma correcta debe existir una
>>>transaccion ANTES de la invocacion a este procedmiento almacenado.
>>>Del porque sumamos primero y luego obtenemos? De esta forma nos
>
> aseguramos
>
>>>de que estamos bloqueando la fila en forma exclusiva antes de leer el
>>
>>valor.
>>
>>>Si no lo hacemos asi podemos estar leyendo los mismos valores si 2
>>>transacciones acceden al mismo momento al procedimiento almacenado para
>
> la
>
>>>misma fila!!!.
>>>
>>>Ahora que lo pienso a este procedimiento le esta faltando control de
>>>errores! que pasa si lo invoco con un valor en sqname que no existe en
>
> la
>
>>>tabla?. Uno siempre piensa que este tipo de situaciones nunca pasan

pero

>>
>>la
>>
>>>realidad nos demuestra que SIEMPRE PASAN! y este tipo de error es uno

de

>>
>>los
>>
>>>mas dificiles de detectar cuando probamos o ponemos en produccion a
>>
>>nuestros
>>
>>>desarrollos. Yo soy de los que piensan que hay que cuidarse en salud,
>
> asi
>
>>>que aqui esta el codigo modificado...
>>>
>>>create procedure nextval
>>> @sqname char(128), ), @retval int OUTPUT
>>>as
>>>IF NOT EXIST( SELECT * FROM generatepk WHERE gpk_pk = @sqname)
>>> RAISERROR 'No existe una secuencia para la clave: ' + sqname , 16,

1

>>>WITH SETERROR
>>>
>>>UPDATE generatepk
>>>SET
>>> gpk_currentnumber = gpk_currentnumber + 1
>>>WHERE
>>> gpk_pk = @sqname
>>>
>>> select @retval = gpk_currentnumber
>>> from generatepk
>>> where gpk_pk = @sqname
>>>
>>> return
>>>
>>>Desde ya una mejor opcion (para algunos discutible) de insertar el
>
> mensage
>
>>>de error en la tabla sysmessage e invocar directamente al RAISERROR con
>>
>>esta
>>
>>>opcion.
>>>
>>>Para invocar a este procedimiento almacenado deberias utilizar algo
>>
>>parecido
>>
>>>a esto:
>>>
>>>BEGIN TRAN
>>>DECLARE @numero INT
>>>EXEC nextval 'SQEMPLEADOS' , @numero
>>>IF @@ERROR <> 0
>>>BEGIN
>>> ... tratamos aqui al error
>>>END
>>>
>>>...
>>>
>>>COMMIT TRAN
>>>Saludos
>>>Adrian D. Garcia
>>>MCSD
>>>NDSoft Consultoria y Desarrollo
>>>
>>>"news.microsoft.com" wrote in message
>>>news:
>>>
>>>>Hola lista,
>>>>
>>>>Soy nuevo en T-SQL, asi que antes que nada quiero pedir disculpas
>>>>si esta inquietud resulta ofensiva para alguien.
>>>>
>>>>Tengo una tabla para generar numeros correlativos de la forma:
>>>>
>>>>pkname pkvalue
>>>>SQNOFACT 4566
>>>>SQNOEMP 981
>>>>
>>>>Quisiera definir un procedimiento almacenado que lea los valores de la
>>>>tabla, de acuerdo al parametro de pkname pasado al procedimiento y
>
> luego
>
>>>>retorne el valor de la columna pkvalue y despues incremente en 1 el
>>
>>valor
>>
>>>>esa columna. Este procedimiento se debe ejecutar como una transaccion
>>
>>para
>>
>>>>evitar errores si hay accesos recurrentes al mismo registro.
>>>>
>>>>Siguiendo la ayuda he definido un pequeño borrador pero no logro que
>>>>funcione correctamente:
>>>>
>>>>create procedure
>>>> nextval @sqname char(128), @retval int OUTPUT
>>>>as
>>>>
>>>>select @retval = gpk_currentnumber
>>>> from
>>>> generatepk where gpk_pk = @sqname
>>>>
>>>>
>>>>return @retval
>>>>
>>>>pero al ejecutarlo asi:
>>>>execute nextval SQEMPLEADOS,1
>>>>
>>>>No devuelve ningun dato pero tampoco genera ningun error, agradecere
>>
>>mucho
>>
>>>>su colaboracion.
>>>>
>>>>Sinceramente,
>>>>
>>>>Josue Maldonado.
>>>>
>>>>
>>>>
>>>
>>>
>>"Adrian Garcia" wrote in message
>>news:
>>
>>>hmm... creo que esto sería asi...
>>>
>>>create procedure
>>> nextval @sqname char(128), ), @retval int OUTPUT
>>>as
>>>UPDATE generatepk
>>>SET
>>> gpk_currentnumber = gpk_currentnumber + 1
>>>WHERE
>>> gpk_pk = @sqname
>>>
>>> select @retval = gpk_currentnumber
>>> from generatepk
>>> where gpk_pk = @sqname
>>>
>>> return
>>>
>>>Desde ya para que esto funcione en forma correcta debe existir una
>>>transaccion ANTES de la invocacion a este procedmiento almacenado.
>>>Del porque sumamos primero y luego obtenemos? De esta forma nos
>
> aseguramos
>
>>>de que estamos bloqueando la fila en forma exclusiva antes de leer el
>>
>>valor.
>>
>>>Si no lo hacemos asi podemos estar leyendo los mismos valores si 2
>>>transacciones acceden al mismo momento al procedimiento almacenado para
>
> la
>
>>>misma fila!!!.
>>>
>>>Ahora que lo pienso a este procedimiento le esta faltando control de
>>>errores! que pasa si lo invoco con un valor en sqname que no existe en
>
> la
>
>>>tabla?. Uno siempre piensa que este tipo de situaciones nunca pasan

pero

>>
>>la
>>
>>>realidad nos demuestra que SIEMPRE PASAN! y este tipo de error es uno

de

>>
>>los
>>
>>>mas dificiles de detectar cuando probamos o ponemos en produccion a
>>
>>nuestros
>>
>>>desarrollos. Yo soy de los que piensan que hay que cuidarse en salud,
>
> asi
>
>>>que aqui esta el codigo modificado...
>>>
>>>create procedure nextval
>>> @sqname char(128), ), @retval int OUTPUT
>>>as
>>>IF NOT EXIST( SELECT * FROM generatepk WHERE gpk_pk = @sqname)
>>> RAISERROR 'No existe una secuencia para la clave: ' + sqname , 16,

1

>>>WITH SETERROR
>>>
>>>UPDATE generatepk
>>>SET
>>> gpk_currentnumber = gpk_currentnumber + 1
>>>WHERE
>>> gpk_pk = @sqname
>>>
>>> select @retval = gpk_currentnumber
>>> from generatepk
>>> where gpk_pk = @sqname
>>>
>>> return
>>>
>>>Desde ya una mejor opcion (para algunos discutible) de insertar el
>
> mensage
>
>>>de error en la tabla sysmessage e invocar directamente al RAISERROR con
>>
>>esta
>>
>>>opcion.
>>>
>>>Para invocar a este procedimiento almacenado deberias utilizar algo
>>
>>parecido
>>
>>>a esto:
>>>
>>>BEGIN TRAN
>>>DECLARE @numero INT
>>>EXEC nextval 'SQEMPLEADOS' , @numero
>>>IF @@ERROR <> 0
>>>BEGIN
>>> ... tratamos aqui al error
>>>END
>>>
>>>...
>>>
>>>COMMIT TRAN
>>>Saludos
>>>Adrian D. Garcia
>>>MCSD
>>>NDSoft Consultoria y Desarrollo
>>>
>>>"news.microsoft.com" wrote in message
>>>news:
>>>
>>>>Hola lista,
>>>>
>>>>Soy nuevo en T-SQL, asi que antes que nada quiero pedir disculpas
>>>>si esta inquietud resulta ofensiva para alguien.
>>>>
>>>>Tengo una tabla para generar numeros correlativos de la forma:
>>>>
>>>>pkname pkvalue
>>>>SQNOFACT 4566
>>>>SQNOEMP 981
>>>>
>>>>Quisiera definir un procedimiento almacenado que lea los valores de la
>>>>tabla, de acuerdo al parametro de pkname pasado al procedimiento y
>
> luego
>
>>>>retorne el valor de la columna pkvalue y despues incremente en 1 el
>>
>>valor
>>
>>>>esa columna. Este procedimiento se debe ejecutar como una transaccion
>>
>>para
>>
>>>>evitar errores si hay accesos recurrentes al mismo registro.
>>>>
>>>>Siguiendo la ayuda he definido un pequeño borrador pero no logro que
>>>>funcione correctamente:
>>>>
>>>>create procedure
>>>> nextval @sqname char(128), @retval int OUTPUT
>>>>as
>>>>
>>>>select @retval = gpk_currentnumber
>>>> from
>>>> generatepk where gpk_pk = @sqname
>>>>
>>>>
>>>>return @retval
>>>>
>>>>pero al ejecutarlo asi:
>>>>execute nextval SQEMPLEADOS,1
>>>>
>>>>No devuelve ningun dato pero tampoco genera ningun error, agradecere
>>
>>mucho
>>
>>>>su colaboracion.
>>>>
>>>>Sinceramente,
>>>>
>>>>Josue Maldonado.
>>>>
>>>>
>>>>
>>>
>>>
>>
>
>

Hola Adrian,

Gracias por la ayuda, efectivamente ahora el codigo funciona
correctamente. Quiero comentarte que un amigo tambien me ha sugerido
este codigo que el asegura que jamas tendre problemas con accesos
recurentes debido a que el update en si mismo primero bloquea.

create procedure nextval
@csqname varchar(30),
@nval int output
as
update sqences
set @nval = sqe_intval = sqe_intval + 1
where sqe_name = @csqname
select @nval
return

¿Que opinion te merece?


Sinceramente,

Josué Maldonado.

Efectivamente, el UPDATE bloquea inmediatamente, de echo es casi el mismo
codigo que te envie, salvo que yo realizo una verificacion antes de
actualizar para ver si existe la clave de busqueda (por eso el IF
EXISTS( ) ) y en caso de no encontrala genera un error para que sea
atrapado por tu aplicacion.
Asi tal cual esta el procedimiento que envias si por error lo invocarias con
un nombre ed secuencia que no existe obtendrias el valor NULL como retorno,
luego tu codigo mas adelante generaria el error de que se esta tratando de
insertar un valor NULL en una clave primaria o algo parecido.

Saludos
Adrian D. Garcia
MCSD
NDSoft Consultoria y Desarrollo

"Josué Maldonado" <josue|at@|lamundial.hn> wrote in message
news:%

Hola Adrian,

Gracias por la ayuda, efectivamente ahora el codigo funciona
correctamente. Quiero comentarte que un amigo tambien me ha sugerido
este codigo que el asegura que jamas tendre problemas con accesos
recurentes debido a que el update en si mismo primero bloquea.

create procedure nextval
@csqname varchar(30),
@nval int output
as
update sqences
set @nval = sqe_intval = sqe_intval + 1
where sqe_name = @csqname
select @nval
return

¿Que opinion te merece?


Sinceramente,

Josué Maldonado.