Re: Wrong returnvalue on userdef function
Hi Peter,
see attached the tabeldata's SQL and the function itsself
---------------------------------------------------------
CREATE DATABASE IF NOT EXISTS `mand27` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `mand27`;
-- MySQL dump 10.13 Distrib 8.0.16, for Win64 (x86_64)
--
-- Host: 10.144.4.53 Database: mand27
-- ------------------------------------------------------
-- Server version 5.7.24
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
SET NAMES utf8 ;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `synewnumber`
--
DROP TABLE IF EXISTS `synewnumber`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SET character_set_client = utf8mb4 ;
CREATE TABLE `synewnumber` (
`TableName` varchar(64) COLLATE latin1_german1_ci NOT NULL,
`LastNumber` int(18) DEFAULT NULL,
PRIMARY KEY (`TableName`),
UNIQUE KEY `synewnumber1` (`TableName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `synewnumber`
--
LOCK TABLES `synewnumber` WRITE;
/*!40000 ALTER TABLE `synewnumber` DISABLE KEYS */;
INSERT INTO `synewnumber` VALUES ('SG_ADRESSEN',4067),('SG_ADR_BANKVERB',3266),('sg_adr_bemerkung',5134),('SG_ADR_DOKUMENTE',2),('sg_adr_komdaten',44889),('sg_auf_alarm',1),('sg_auf_altart',6),('SG_AUF_ALTERNATIVEMENGE',0),('sg_auf_ansch',522),('sg_auf_artikel',404973),('sg_auf_drudef',2665),('sg_auf_formeln',0),('SG_AUF_FPOS',279837),('sg_auf_fschrift',73990),('sg_auf_kasse',15),('sg_auf_kassenabschlussjournal',439),('sg_auf_kassenmoduljournal',3765),('SG_AUF_KGRGRUP',5),('sg_auf_kundform',0),('sg_auf_kunpreis',14739),('sg_auf_lager',47),('SG_AUF_LGRGRUP',7),('sg_auf_liefart',15341),('sg_auf_liepreis',36),('sg_auf_nummern',1),('sg_auf_ramatrix',404973),('sg_auf_snr',37),('sg_auf_snr_historie',77),('sg_auf_txtbaust',3),('sg_auf_versart',12),('SG_AUF_VERT',3),('sg_auf_vkpreis',413203),('sg_auf_wgruppe',315),('sg_auf_zahlart',13),('sg_epg_catalogs',8),('sg_epg_products_catalogs',58),('sg_epg_soaplog',0),('sg_epg_tasklog',9596),('sg_fib_anlagen',7),('SG_FIB_AnlagenGruppen',21),('sg_fib_anlagen_buchungen_matrix',11),('sg_fib_belegarchiv',52475),('sg_fib_belegnrkreise',54),('SG_FIB_BUCH',351059),('sg_fib_buchungstexte',0),('SG_FIB_BUCH_ISTVERSTEUERER',89),('sg_fib_buch_stapel_prot',7526),('SG_FIB_Budget',31989),('sg_fib_bwa_posdef',1140),('sg_fib_datevexporte',42),('sg_fib_eaonline',5298),('SG_FIB_EAProtokolle',8724),('SG_FIB_EAUmsaetze',36489),('SG_FIB_FSTAPEL',4255),('sg_fib_gliederungen',262),('sg_fib_gliederungen_konten',927),('sg_fib_konten',31041),('sg_fib_konten_klassen',40),('sg_fib_kststellen',2),('SG_FIB_OPOS',54948),('SG_FIB_OPOS_POS',98434),('sg_fib_perioden',72),('sg_fib_sepamandate',48),('SG_FIB_SepaMandate_Historie',2413),('sg_fib_sitzungen',2335),('sg_fib_steuersaetze',54),('sg_fib_wijahre',6),('SG_FIB_ZWISCHENSTAPEL',494284),('SG_KUNDEN',3396),('SG_LIEFERANTEN',671),('sg_mahn_formulare',184),('sg_mahn_kuvorgaben',80395),('sg_mahn_mahnungen',2187),('SG_SYS_BEMERKTYP',2),('SG_SYS_DBProtokoll',1114522),('SG_SYS_DbSchemaVersion',1490),('SG_Sys_Elster_Protokoll',54),('sg_sys_objektdaten',744),('sg_sys_vorgaben',365);
/*!40000 ALTER TABLE `synewnumber` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2019-07-08 8:08:51
______________________________________________________________________
CREATE DEFINER=`mysql`@`%` FUNCTION `getNextSysNumber`( c_number_type varchar(64)) RETURNS int(18)
BEGIN
SELECT LastNumber
INTO @nextnumber
FROM synewnumber
WHERE UPPER(TRIM(synewnumber.TableName)) = UPPER(TRIM(c_number_type));
SET @nextnumber = @nextnumber + 1;
RETURN ( @nextnumber );
END