Following is a complete and commented example of how to simulate passing a vector or array as a parameter to a Stored Procedure .
CREATE PROCEDURE SIMULA_ARRAY_COMPLETA
@SCOCPFCNPJ VARCHAR(8000),
@SCOCLASSIFICACAO VARCHAR(8000),
@SDEJUSTIFICATIVA VARCHAR(8000)
/* .... OUTROS PARÂMETROS SE EXISTIREM .... */
AS DECLARE @CPFCNPJ VARCHAR(14),
@CLASSIFICACAO CHAR(1),
@JUSTIFICATIVA VARCHAR(255),
@DELIMITADOR VARCHAR(2)
--Define que vai ser o delimitador
SET @DELIMITADOR = '@|'
--Inicia a transação
BEGIN TRAN
--CONCATENA O @DELIMITADOR NO FINAL DE DAS VARIÁVEIS LOCAIS
IF LEN(@SCOCPFCNPJ) > 0 SET @SCOCPFCNPJ = @SCOCPFCNPJ + @DELIMITADOR
IF LEN(@SCOCLASSIFICACAO) > 0 SET @SCOCLASSIFICACAO = @SCOCLASSIFICACAO + @DELIMITADOR
IF LEN(@SDEJUSTIFICATIVA) > 0 SET @SDEJUSTIFICATIVA = @SDEJUSTIFICATIVA + @DELIMITADOR
--INICIA LOOP PARA EXTRAIR SCOCPFCNPJ PARA EFETUAR A ATUALZIAÇÃO
WHILE LEN(RTRIM(LTRIM(@SCOCPFCNPJ))) > 0
BEGIN --LOCALIZA E EXTRAI O CNPJ/CPF, @CLASSIFICACAO E JUSTIFICATIVA
--PARA VARIAVEIS LOCAIS
SELECT @CPFCNPJ = SUBSTRING(@SCOCPFCNPJ, 1, CHARINDEX(@DELIMITADOR, @SCOCPFCNPJ) - 1)
SELECT @CLASSIFICACAO = SUBSTRING(@SCOCLASSIFICACAO, 1, CHARINDEX(@DELIMITADOR, @SCOCLASSIFICACAO) - 1)
SELECT @JUSTIFICATIVA = SUBSTRING(@SDEJUSTIFICATIVA , 1, CHARINDEX(@DELIMITADOR, @SDEJUSTIFICATIVA ) - 1)
INSERT INTO NOME DA TABELA (
CO_CPF_CNPJ, CO_CLASSIFICACAO, DE_JUSTIFICATIVA)
VALUES( @CPFCNPJ, @CLASSIFICACAO, @JUSTIFICATIVA)
--RETIRA LOCALIZA E EXTRAI O CNPJ/CPF, SITUAÇÃO E JUSTIFICATIVA
SELECT @SCOCPFCNPJ= SUBSTRING(@SCOCPFCNPJ, CHARINDEX(@DELIMITADOR, @SCOCPFCNPJ) + 2, LEN(@SCOCPFCNPJ))
SELECT @SCOCLASSIFICACAO=
SUBSTRING(@SCOCLASSIFICACAO, CHARINDEX(@DELIMITADOR, @SCOCLASSIFICACAO) + 2, LEN(@SCOCLASSIFICACAO))
SELECT @SDEJUSTIFICATIVA =
SUBSTRING(@SDEJUSTIFICATIVA , CHARINDEX(@DELIMITADOR, @SDEJUSTIFICATIVA ) + 2, LEN(@SDEJUSTIFICATIVA ))
END
--VERIFICA OCORRÊNCIA DE ERROS DURANTE O PROCESSO PARA CONFIRMAR OU NÃO A TRANSAÇÃO
IF @@ERROR = 0
BEGIN COMMIT TRAN
END
ELSE
BEGIN ROLLBACK TRAN
END
Considerations:
In the presentation layer (in ASP for example) you can create the
array's normally.
In the stored procedure call, the array must be transformed into
string's delimited.
You can use any delimiter normally used by "@ |" Yeah
a combination that is unlikely to be used primarily
in the field of description and justifications.
In the case of justifications, care must be taken to divide the size
total field varchar in sp (8000) by justification size
to avoid overflow in size. Example a justification of 255
can be passed to 31 occurrences of the vector (8000/255) if
need to pass more loop in the application and go from 31 in
31.
To transform a vector into a delimited string in ASP you can
use via command and pass sVar as sp parameter:
sVar = join (your_vector, "@ |")
This material was created by me was created on March 23, 2007 as a tutorial and may be dated, but I believe it can be used in some cases. Of course you should always be careful when using strings
because it affects performance.
Reference:
Simula SPLIT (Array) SQL