Until the% of call of the parameters takes all data correctly, the problem is at the time of going to the procedure via ADO.net, that for example, I pass the values, function
and in the procedure it picks up only the first character.
function call vb.net code:
Friend Function consultarTotalValorHonra(ByVal objectFactory As ObjectFactory, ByVal toAgendPagtoHonra As ITOAgendPagtoHonra) As ITOAgendPagtoHonra
Dim query As String = "PROC_LISTAR_TOTAL_VALOR_HONRA"
Dim conString As String = objectFactory.getSBSeguranca.ConnectionString("SEN")
Dim con As New SqlConnection(conString)
Dim cmd As New SqlCommand(query, con)
con.Open()
cmd.CommandType = CommandType.StoredProcedure
'Data Arquivo
cmd.Parameters.Add(New SqlParameter("@PDAT_DATAARQUIVO", SqlDbType.DateTime, ParameterDirection.Input))
cmd.Parameters("@PDAT_DATAARQUIVO").IsNullable = True
If toAgendPagtoHonra.getDataArquivo = "01/01/1753" Or toAgendPagtoHonra.getDataArquivo() = Nothing Then
cmd.Parameters("@PDAT_DATAARQUIVO").Value = DBNull.Value
Else
cmd.Parameters("@PDAT_DATAARQUIVO").Value = toAgendPagtoHonra.getDataArquivo()
End If
'Status
cmd.Parameters.Add(New SqlParameter("@PSTR_STATUS", SqlDbType.Int, ParameterDirection.Input))
cmd.Parameters("@PSTR_STATUS").IsNullable = True
cmd.Parameters("@PSTR_STATUS").Value = toAgendPagtoHonra.getStatus()
'Lista de CodOperacao
cmd.Parameters.Add(New SqlParameter("@OPE_NROOPERACAO", SqlDbType.VarChar, ParameterDirection.Input))
cmd.Parameters("@OPE_NROOPERACAO").IsNullable = True
cmd.Parameters("@OPE_NROOPERACAO").Value = toAgendPagtoHonra.getListCodOperacao()
Dim dt As New DataTable()
Dim adapter As New SqlDataAdapter(cmd)
adapter.Fill(dt)
con.Close()
Return objectFactory.getITOAgendPagtoHonra(dt)
End Function
Procedure:
ALTER PROCEDURE [dbo].[PROC_LISTAR_TOTAL_VALOR_HONRA]
(
@PDAT_DATAARQUIVO DATETIME
, @PSTR_STATUS INT
, @OPE_NROOPERACAO VARCHAR(MAX)
)
AS
BEGIN
BEGIN TRY
DECLARE @delimiter VARCHAR(1) = ','
DECLARE @result TABLE(OPE_OPERACAO NVARCHAR(MAX))
DECLARE @COMECO INT, @FIM INT
SELECT @COMECO = 1, @FIM = CHARINDEX(@delimiter, @OPE_NROOPERACAO)
WHILE @COMECO < LEN(@OPE_NROOPERACAO) + 1 BEGIN
IF @FIM = 0
SET @FIM = LEN(@OPE_NROOPERACAO) + 1
INSERT INTO @result (OPE_OPERACAO)
VALUES(SUBSTRING(@OPE_NROOPERACAO, @COMECO, @FIM - @COMECO))
SET @COMECO = @FIM + 1
SET @FIM = CHARINDEX(@delimiter, @OPE_NROOPERACAO, @COMECO)
END
DECLARE @TABRESULTADO TABLE(TOT_VALOR_HNR DECIMAL(15,2))
INSERT INTO @TABRESULTADO(TOT_VALOR_HNR)
SELECT SUM(OPE_VALORAVAL) AS 'TOT_VALOR_HNR'
FROM OPERACOES
INNER JOIN EMPRESAS ON (OPE_CNPJ = EMP_CNPJ)
INNER JOIN SITUACOES ON (OPE_CODIGOSITUACAO = SIT_CODIGOSITUACAO)
INNER JOIN @result ON(OPE_NROOPERACAO = OPE_OPERACAO)
LEFT JOIN HONRA_FLAGS ON (OPE_CODIGOBANCO = HRF_CODIGOBANCO
AND OPE_CODIGOAGENCIA = HRF_CODIGOAGENCIA
AND OPE_CODIGOPRODUTOBANCO = HRF_CODIGOPRODUTOBANCO
AND OPE_NROOPERACAOBANCO = HRF_NROOPERACAOBANCO
AND HRF_DATAINCLUSAO = SIT_DATAINCLUSAO)
WHERE
SIT_DATAINCLUSAO >= CONVERT(VARCHAR(10), @PDAT_DATAARQUIVO, 126)
AND OPE_CODIGOSITUACAO = @PSTR_STATUS
AND CAST(OPE_NROOPERACAO AS VARCHAR(60)) in (OPE_OPERACAO)
SELECT * FROM @TABRESULTADO
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorLine INT;
DECLARE @ErrorNumber INT;
SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(), @ErrorLine = ERROR_LINE();
SELECT @ErrorMessage = 'Error:' + convert(varchar(20), @ErrorNumber) + '-' + @ErrorMessage + 'Line:' + convert(varchar(20), @ErrorLine);
RAISERROR (@ErrorMessage, 16, 1);
RETURN
END CATCH
END
Note:
I've done everything, even put it in the declaration of the variable @OPE_NROOPERACAO = '34567,32135'
as @OPE_NROOPERACAO
or lower value and nothing ...
Does anyone know how to solve this?