-- SCRIPT CREATED BY : MAURO C. PICHILIANI
-- DATE : 6/17/2001
-- STORED PROCEDURE PROC_PARSER AND EXAMPLES OF USE
-----------------------------------------------------

-- DROP THE PROCEDURE 
DROP PROCEDURE PROC_PARSER
GO 

CREATE PROCEDURE PROC_PARSER(@STR_LISTA VARCHAR(500))
AS

-- DECLARATION OF VARIABLES 
DECLARE @VEZ VARCHAR(1)          -- THE CHARACTER READ
DECLARE @CONTADOR INT            -- THE COUNTER USED TO READ THE NEXT CHARACTER
DECLARE @ACUMULA  VARCHAR(50)    -- THE TOKEN READ
DECLARE @INSERE VARCHAR(100)     -- THE STRING THAT WILL BE INSERTED IN THE TABLE
DECLARE @VALOR BIT               -- FLAG:  0-> THERE IS A " IN THE VALUE OF THE PARAMETER
                                 --        1-> NO " IN THE VALUE OF THE PARAMETER

-- NO MESSAGES
SET NOCOUNT ON


-- CREATE A TEMPORARY TABLE THAT WILL RETURN THE DATA
CREATE TABLE #PARAMETROS
(
	NOME  VARCHAR(50) NULL,
	VALOR VARCHAR(100) NULL,
)



-- BY DEFAULT , NO " IN THE VALUE OF THE PARAMETER
SELECT @VALOR = 0 

SELECT @ACUMULA=''

SELECT @CONTADOR = 1

-- REMOVE BLANK SPACES 
SELECT @STR_LISTA = RTRIM(LTRIM(@STR_LISTA))

-- DO A LOOP TO GET ONE CHARACTER AT TIME
WHILE @CONTADOR <= DATALENGTH(@STR_LISTA)
BEGIN
	-- GET ONE CHARACTER AT TIME
	SELECT @VEZ = SUBSTRING(@STR_LISTA,@CONTADOR,1)

	-- IF THERE IS A '=' AND THE '=' INS'T IN A VALUE OF A PARAMETER
	IF  @VEZ = '=' AND @VALOR = 0
	BEGIN
		
		-- REMOVE BLANK SPACES 		
		SELECT @ACUMULA = LTRIM(RTRIM(@ACUMULA))
		
		-- IF THE STRING CONTAINS A PARAMETER NAME AND VALUE...
		IF CHARINDEX(' ',@ACUMULA) > 0 
		BEGIN
			
			IF CHARINDEX('"',@ACUMULA) > 0 -- WE GOT THE "
			BEGIN
				-- GET THE VALUE  INSIDE  THE "" 
				SELECT  @INSERE  = SUBSTRING(@ACUMULA,1,CHARINDEX('"',@ACUMULA,2))

				-- PUT THE VALUE IN THE CORRECT PARAMETER
				UPDATE #PARAMETROS SET VALOR = RTRIM(LTRIM(@INSERE))
				WHERE VALOR IS NULL

				-- GET THE NEXT PARAMETER NAME
				SELECT  @INSERE  = SUBSTRING(@ACUMULA , CHARINDEX('"',@ACUMULA,2)  + 1, DATALENGTH(@ACUMULA) ) 
				
				-- INSERT THE NEXT PARAMETER IN THE TEMP. TABLE
				INSERT INTO #PARAMETROS VALUES(RTRIM(LTRIM(@INSERE)),NULL)

			END
			ELSE  -- NO " ,  ' ' MEANS THAT WE HAVE TWO VALUES: ONE VALUE AND THE NEXT PARAMETER NAME
			BEGIN
				-- GET THE PARAMETER VALUE
				SELECT  @INSERE  = SUBSTRING(@ACUMULA,1,CHARINDEX(' ',@ACUMULA))
	
				-- PUT THE VALUE IN THE CORRECT PARAMETER
				UPDATE #PARAMETROS SET VALOR = RTRIM(LTRIM(@INSERE))
				WHERE VALOR IS NULL
	
				-- GET THE NEXT PARAMETER NAME
				SELECT  @INSERE  = SUBSTRING(@ACUMULA,CHARINDEX(' ',@ACUMULA),DATALENGTH(@ACUMULA))
				
				-- INSERT THE NEXT PARAMETER IN THE TEMP. TABLE
				INSERT INTO #PARAMETROS VALUES(RTRIM(LTRIM(@INSERE)),NULL)
			END

		END
		ELSE  
			-- INSERT THE NAME OF THE PARAMETER IN THE TEMP. TABLE
			INSERT INTO #PARAMETROS VALUES(@ACUMULA,NULL)

		-- CLEAN THE TOKEN
		SELECT @ACUMULA = ''
	END
	ELSE
	BEGIN	
		-- IF THERE IS " IN A VALUE OF A PARAMETER , SWITCH THE VALUE OF THE FLAG @VALOR
		IF @VEZ = '"'
			IF @VALOR = 0
				SELECT @VALOR = 1
			ELSE
				SELECT @VALOR = 0
		
		-- ACUMULATE THE STRING....
		SELECT @ACUMULA = @ACUMULA + @VEZ

	END

	-- INCREASE DE COUNTER
	SELECT @CONTADOR =  @CONTADOR +1
END

-- FILL THE LAST VALUE OF A PARAMETER WITH WHAT WAS LEFT FROM THE STRING....
UPDATE #PARAMETROS SET VALOR = RTRIM(LTRIM(@ACUMULA))
WHERE VALOR IS NULL

-- RETURN ALL PARAMETERS AND THE VALUES
SELECT * FROM #PARAMETROS

-- SET  MESSAGES ON
SET NOCOUNT OFF

GO


/* ----------------------------------------------------
USE OF THE STORED PROCEDURE:
1º ARGUMENT: THE STRING WITH THE PARAMETER LIST. FORMAT:

'PAR_NAME1=PAR_VALUE1 PAR_NAME2=PAR_VALUE2 PAR_NAME3=PAR_VALUE3 ...'

I.E:

'X=123 Y=456'
' X = "ABC" Z= TTT Y=12TEE Z=" "'


OBSERVATION: IF YOU WHAT TO USE BLANK SPACES IN A PARAMETER VALUE , YOU MUST USE IT WITH A 
QUOTATION MARK ( " ). SEE THE EXAMPLE ABOVE.THE PROCEDURE DON'T CHECK IF THE STRING LIST
IS CORRECT , JUST RETURN THE RIGHT VALUES FOR THE PARAMETERS

*/


/* EXAMPLES OF USE */

EXEC PROC_PARSER 'X=10   Y="35"  Z=ABC W="LALA=2"'

EXEC PROC_PARSER 'X=ÉÉÉ Y="35" Z=ABC W="LA= = =L A"'

EXEC PROC_PARSER 'X="A Z" Y="B"'

EXEC PROC_PARSER 'X = 10   Y ="35"  Z =ABC W="LA LA" '

EXEC PROC_PARSER 'X=10   Y=''A'' Z=C W="_ A" K="E=''1234''"'

EXEC PROC_PARSER 'X= 10   Y=''A'' Z= C W=" _ A " K =" E='' 1 2 34'' TE "'

EXEC PROC_PARSER '  PAR1  =   10 PAR2 = -10 PAR3= "WIDTH = 50"  '

EXEC PROC_PARSER 'X=10   Y=35  Z=ABC W="TESTE=2"'


/*****************************************************************************************/
/* END OF FILE */