I am trying to create a stored procedure where the MEMBER ID is custom auto-incremented as follows
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
But when I run the query again, it throws me the following error message
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)