Here is a complete and commented example of how to simulate the passage of a vector or array as a parameter for 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 call, the array s must be transformed into
bounded string s.
- You can use any delimiter normally using "@|" because it is
a combination that will probably not be used mainly
in the field of description and justifications.
- In the case of justifications, care should be taken to divide the size
total varchar field in sp (8000) by the size of the justification
to avoid bursting in size. Example a justification of 255
may be passed to 31 vector occurrences (8000/255) if
need to spend more loop the application and pass 31 in
31.
To transform a vector into a bounded string in ASP can be
use via command and pass svar as parameter of sp:
svar = Join(you_vector,"@|")
This material was created by me was created on 23 March 2007 as a tutorial and can this 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:
Simulates SPLIT (Array) SQL
Matthew has no way to send an array, you will have to do gambiarras, own experience. sending as varchar equal to friend commented vc tb loses great performance of SQL pq a stored will no longer be compiled because of execute @query
– Dorathoto