Error in SQL 2018 Custom Map

All we need is an easy explanation of the problem, so here it is.

Good day.
I am trying to create a stored procedure where the MEMBER ID is custom auto-incremented as follows

"AP202170001"
"AP202170002"
"AP202170003"

and so on as it goes by registering data in the affiliate table.

I have the following query that shows me what I want

SELECT 'AP' + DATENAME(YEAR,GETDATE())+ CONVERT(VARCHAR(2),MONTH(GETDATE()))+ RIGHT(CAST((POWER(10, 6) +
 CAST(ISNULL(MAX(IDAFILIADO), 0) + 1 AS VARCHAR(5))) AS VARCHAR(7)), 6)
AS IDAFILIADO
from AUT_AFILIADO_PUNTOS

Error in SQL 2018 Custom Map

But when I run the query again, it throws me the following error message

Error in SQL 2018 Custom Map

It is not adding the correlative or in which part I am making a mistake, the data type of the IDAFILIADO field is char (13)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

On heavy duty server this could cause trouble if you define IDAFILIADO as unique or primary key

This will also work with two digits month numbers

CREATE TABLE AUT_AFILIADO_PUNTOS (IDAFILIADO VARCHAR(20))
GO
INSERT INTO AUT_AFILIADO_PUNTOS VALUES('AP202170001'),( 'AP202170002'),( 'AP202170003')
GO
SELECT 'AP' + DATENAME(YEAR,GETDATE())+ CONVERT(VARCHAR(2),MONTH(GETDATE()))+ RIGHT(CAST((POWER(10, 6) +
  CAST((ISNULL(MAX(SUBSTRING(IDAFILIADO, 3,  LEN(IDAFILIADO))), 0) + 1) % (POWER(10, 4)) AS VARCHAR(5))) AS VARCHAR(7)), 6)
AS IDAFILIADO
from AUT_AFILIADO_PUNTOS
GO
| IDAFILIADO    |
| :------------ |
| AP20217000004 |
SELECT 'AP' + DATENAME(YEAR,GETDATE())+ CONVERT(VARCHAR(2),MONTH('2020-11-01'))+ RIGHT(CAST((POWER(10, 6) +
  CAST((ISNULL(MAX(SUBSTRING(IDAFILIADO, 3,  LEN(IDAFILIADO))), 0) + 1) % (POWER(10, 4)) AS VARCHAR(5))) AS VARCHAR(7)), 6)
AS IDAFILIADO
from AUT_AFILIADO_PUNTOS
GO
| IDAFILIADO     |
| :------------- |
| AP202111000004 |

db<>fiddle here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply