1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
|
ALTER FUNCTION [dbo].[NUMCDE] (@natp AS varchar(10), @numBl AS int) returns varchar(80) AS BEGIN
DECLARE @NDB INT, @NFN INT
DECLARE @CC varchar(80), @NUMCDE varchar(80)
SET @NDB=7
SET @NFN=24
DECLARE MyCursor CURSOR
FOR SELECT
substring (gl_libelle,PATINDEX('%client%',gl_libelle)+@NDB,len(gl_libelle)-((PATINDEX('%client%',gl_libelle))+@NFN))
FROM ligne
WHERE gl_typeligne = 'COM'
and gl_naturepieceg=convert(varchar(10), @natp)
and (PATINDEX('%client%',gl_libelle)+@NDB)<len(gl_libelle)
and gl_numero=convert(varchar(8),@numBl)
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @CC
SELECT @NUMCDE =''
WHILE @@FETCH_STATUS = 0
BEGIN
IF @NUMCDE='' set @NUMCDE=@CC
ELSE
set @NUMCDE = @NUMCDE +' - '+ @CC
FETCH NEXT FROM MyCursor INTO @CC
END
CLOSE MyCursor
DEALLOCATE MyCursor
RETURN @numcde
END
----------------------------------------------EXEMPLE--SELECT [dbo].[NUMCDE]('BLC',14) |
Partager