EXEMPLO_1212310 Database

Parameters

Name	Type	Mode
@NOME_PARAMETRO	varchar	IN

Definition

CREATE FUNCTION FUNC_GETPARAM_TABLETC (@NOME_PARAMETRO VARCHAR(MAX))
RETURNS @CURSOR_PARAMETRO TABLE (
  COLIGADA INT,
  ID       VARCHAR(MAX),
  IDREF    VARCHAR(MAX))
AS
  BEGIN
	  DECLARE @COLIGADA INT
	  DECLARE @VALOR_PARAMETRO VARCHAR(MAX)
	  DECLARE @ID VARCHAR(MAX)
	  DECLARE @IDV VARCHAR(MAX)
	  DECLARE @IDREF VARCHAR(MAX)
	  DECLARE COLIGADAS CURSOR FOR
		SELECT HCPARAMETROS.CODCOLIGADA,
			   HCPARAMETROS.VALORTEXT
		FROM   HCPARAMETROS
		WHERE  ( HCPARAMETROS.CODCOLIGADA <> 0
				 AND HCPARAMETROS.NOMECOLUNA = @NOME_PARAMETRO )
			   AND HCPARAMETROS.VALORTEXT IS NOT NULL
	  OPEN COLIGADAS
	  FETCH NEXT FROM COLIGADAS INTO @COLIGADA, @VALOR_PARAMETRO
	  WHILE @@FETCH_STATUS = 0
		BEGIN
			DECLARE CURSOR_PARAMETRO CURSOR FOR
			  SELECT ITEM
			  FROM   SPLITSTRINGS(@VALOR_PARAMETRO, ';')
			OPEN CURSOR_PARAMETRO
			FETCH NEXT FROM CURSOR_PARAMETRO INTO @IDV
			WHILE @@FETCH_STATUS = 0
			  BEGIN
				  SET @ID = SUBSTRING(@IDV, 1, (CHARINDEX('|', @IDV)- 1))
				  SET @IDREF = SUBSTRING(@IDV, CHARINDEX('|', @IDV)+1, LEN(@IDV))
				  INSERT INTO @CURSOR_PARAMETRO
				  SELECT @COLIGADA,
						 @ID,
						 @IDREF
				  FETCH NEXT FROM CURSOR_PARAMETRO INTO @IDV
			  END
			CLOSE CURSOR_PARAMETRO
			DEALLOCATE CURSOR_PARAMETRO
			FETCH NEXT FROM COLIGADAS INTO @COLIGADA, @VALOR_PARAMETRO
		END
	  CLOSE COLIGADAS
	  DEALLOCATE COLIGADAS
	  RETURN
  END;