Get Sequence Current Value

I have a created sequence on my DB Server, used for generated registers and the detail of every one. I have to create a plain document of that detail in the moment of the generation, so I have to retrieve the current value of the sequence (not the NEXT)

I’m generating a query for get the desired data, but I need (as I said) get the current value of the sequence

— this is my sequence:

CREATE SEQUENCE [dbo].[GuiaRemisionID] 
AS [int]
START WITH 1000
INCREMENT BY 1
MINVALUE -2147483648
MAXVALUE 2147483647
CACHE 
GO

— and this is my SP for retriving data

CREATE PROCEDURE [dbo].[CAJAS_CONS_GUIAREMISION]
AS 
BEGIN 
    DECLARE @ID_GUIAREMISION INT 
    select @ID_GUIAREMISION = current_value FROM sys.sequences WHERE name = 'GuiaRemisionId' ;
    SELECT 
    T1.ID_GUIAREMISION, T1.CLIENTE_CODIGO, T1.USUARIO, T1.FECHA_GUIA, T1.TIPO,
    T2.CAJA_CODIGO, T2.CAJA_NUMERO, T2.ITEM,  
    T2.UBICACION_COORD1,
    T2.UBICACION_COORD2, T2.UBICACION_COORD3, T2.UBICACION_COORD4,
    T2.UBICACION_COORD5, T2.UBICACION_COORD6, T2.DEPARTAMENTO
    FROM GUIAS_REMISION T1, GUIAS_REMISION_DETALLE T2
    WHERE T1.ID_GUIAREMISION = T2.ID_GUIAREMISION
    AND T1.ID_GUIAREMISION = @ID_GUIAREMISION

END

But my problem is: “Implicit conversion of the data type sql_variant to int is not allowed”

How can I get the current value of my sequence? Should I use: select max (id_guiaRemision) from my table? (not seems efficient) Please, I hope any one can help me.

Best regards

Answer

Could you try this?

SELECT
Cast(ISNULL(seq.current_value,N'''') as INT) AS [Current Value]
FROM
sys.sequences AS seq where seq.name = 'GuiaRemisionID'

Advertisement

Answer