Generar numero correlativo o serial

create procedure nextval
@sqname char(128) ,
@retval int OUTPUT
as
select @retval = gpk_currentnumber
from generatepk
where gpk_pk = @sqname
return 0


declare @Proximo int
execute nextval SQEMPLEADOS,@Proximo
select @Proximo

Sin embargo vas a tener "condiciones de carrera" cuando tengas concurrencia
si quieres hacer primero el select y luego el update a menos que uses el
hint holdlock

Otra forma es usando UPDATE unicamente y en la misma clausula haces las dos
cosas actualizas sumando 1 y guardas el valor en una variable..




Jose Mariano Alvarez
Comunidad de base de datos
Grupo de Usuarios Microsoft
www.mug.org.ar


"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.

La sintaxis del UPDATE soporta SET @variable = columna = expresion por lo
que te ahorras el select y lo haces en una sola instruccion. El resto de las
consideraciones que hizo Adrian son totalmente validas aunque uses esta
forma

Ejemplo

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


En lo que mande antes me olvide de definir el parametro como de salida en la
invocacion.




Jose Mariano Alvarez
Comunidad de base de datos
Grupo de Usuarios Microsoft
www.mug.org.ar


"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.
>
>
>

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.
>
>
>

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.

Que bueno que siempre se aprende algo nuevo!.
Gracias Mariano

Saludos
Adrian D. Garcia
MCSD
NDSoft Consultoria y Desarrollo

"Mariano Alvarez (MUG)" <jose.alvarez * *Arroba* * mug.org.ar> wrote in
message news:

La sintaxis del UPDATE soporta SET @variable = columna = expresion por lo
que te ahorras el select y lo haces en una sola instruccion. El resto de

las

consideraciones que hizo Adrian son totalmente validas aunque uses esta
forma

Ejemplo

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


En lo que mande antes me olvide de definir el parametro como de salida en

la

invocacion.




Jose Mariano Alvarez
Comunidad de base de datos
Grupo de Usuarios Microsoft
www.mug.org.ar


"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.
> >
> >
> >
>
>