Problem in converting a query into Vb.net for a varchar model

Question

Until the function call of the parameters picks all the data correctly, the problem is time to pass to the Procedure via ADO.net, that for example, step the values, @OPE_NROOPERACAO = '34567,32135' and in the process it takes 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

Obs:

I’ve done everything, even put in the statement of the variable @OPE_NROOPERACAO as varchar(3000) or lower and nothing...

Someone knows how to fix this?

Answer 1

You can use a TYPE to pass the information you need by following example:

in SQL Voce create TYPE:

CREATE TYPE [dbo].[TPV_VARCHAR_LIST] AS TABLE(
    [Item] [varchar](8000) NULL
)

in Vb Voce uses a Datatable to pass this information to the previous:

Dim dt as New DataTable
dt.Columns.Add("Item")
dr as DataRow = dt.NewRow()
dr("Item") = "34567"
dt.Rows.Add(dt)

cmd.Parameters.Add(New SqlParameter("@OPE_NROOPERACAO", SqlDbType.Structured)
cmd.Parameters("@OPE_NROOPERACAO").Value = dt 

And your trial looks something like this:

ALTER PROCEDURE [dbo].[PROC_LISTAR_TOTAL_VALOR_HONRA]
(
        @PDAT_DATAARQUIVO    DATETIME
    ,   @PSTR_STATUS      INT
    ,   @OPE_NROOPERACAO  AS TPV_VARCHAR_LIST READONLY  

)
AS
BEGIN
    BEGIN TRY

        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 OPE_NROOPERACAO IN (SELECT * FROM @OPE_NROOPERACAO)
            AND 
               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

By not knowing your tables I may have left something wrong, but the line that matters is the line

@OPE_NROOPERACAO  AS TPV_VARCHAR_LIST READONLY 

and

AND OPE_NROOPERACAO IN (SELECT * FROM @OPE_NROOPERACAO)

NOTE: Works only for Sqlcommand