Generate SQL location number

Question

Generate SQL location number

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

I find the need to generate a code for each record that is inserted in the database and add it to the field NumberRadicate, I searched for documentation but the only one I found is the identity autoincremental but the code must have the letters concatenated AG; So the autoincremental would not work, do you have any idea how it can be done?

Thank you very much for your time.

ALTER PROCEDURE [dbo].[PRC_PRUEBA_INSERTAR]
@idRadicado int output,
@NumeroRadicado varchar (50),
@Documento varchar (100),
@TipoSoporte varchar (50),
@Remitente varchar (100),
@AsuntoFactura varchar (100),
@Destinatario varchar (100),
@Observaciones varchar (254),
@DesOpcional varchar (100)
AS
INSERT INTO tblDocumentalP ( 
       [NumeroRadicado]
      ,[Fecha]
      ,[Documento]
      ,[TipoSoporte]
      ,[Remitente]
      ,[AsuntoFactura]
      ,[Destinatario]
      ,[Observaciones]
      ,[DesOpcional])
      VALUES 
      (@NumeroRadicado, 
      SYSDATETIME(), 
      @Documento, 
      @TipoSoporte, 
      @Remitente, 
      @AsuntoFactura, 
      @Destinatario,
      @Observaciones,
      @DesOpcional)

sql-server

asked by Brian Velez 21.09.2018 в 18:40

source

2 answers

1

What comes to me quickly (and I do not know if it is the most optimal), is that you go and see the maximum value of your table and based on that you add a +1, the problem is that if you delete a record , that gap will remain, but this should work based on what you need.

It will have to be improved, it's just an idea

You create these values before your insert, and at the end in the insert you put the @ Value1 or whatever you like to call, but before validating that your table is not empty, and always get the largest number that exists in your table, so it's never going to be repeated.

DECLARE @Valor1 VARCHAR(MAX),
        @Valor2 INT

    SET @Valor1 = (SELECT COUNT('') FROM tblDocumentalP)

    IF @Valor1 = 0
    BEGIN
        SET @Valor1 = (
                SELECT CONVERT(VARCHAR(MAX), @Valor1 + 1) + 'AG'
                )
    END
    ELSE
    BEGIN
        SET @Valor2 = (
                SELECT MAX(CONVERT(INT, REPLACE(NumeroRadicado, 'AG', '')))
                FROM tblDocumentalP
                )
        SET @Valor1 = CONVERT(VARCHAR(MAX), @Valor2 + 1) + 'AG'
    END

I hope and serve you, greetings.

answered by 21.09.2018 в 20:04

Error in my fix in C # Show Input as selected in a Select

score 1 · Accepted Answer

If you have no problems with eventual gaps in the numbering, I think the simplest thing, if you have at least SQL Server 2008, is to create a calculated column

CREATE TABLE #Ejemplo (
    ID              INT IDENTITY,
    NumeroRadicado  AS ('AG' + RIGHT(REPLICATE('0', 15) + CONVERT(VARCHAR, ID),15)) PERSISTED NOT NULL,
    Dato            VARCHAR(255)
)

INSERT INTO #Ejemplo (Dato) VALUES ('Dato1') , ('Dato2')

SELECT  *
    FROM #Ejemplo

Exit:

╔══════╦═══════════════════╦═══════╗
║ ID   ║ NumeroRadicado    ║ Dato  ║
╠══════╬═══════════════════╬═══════╣
║ 1,00 ║ AG000000000000001 ║ Dato1 ║
╠══════╬═══════════════════╬═══════╣
║ 2,00 ║ AG000000000000002 ║ Dato2 ║
╚══════╩═══════════════════╩═══════╝

Notes:

As you will see, we combine a IDENTITY with the fixed string AG , the padding of 0 is optional but useful since it unifies the sort criterion
Important: columns calculated by default do not physically exist, so they are not used as indexes, unless you materialize them by PERSISTED NOT NULL .