Problem in converting a query in vb.net to a procedure with varchar

0

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?

    
asked by anonymous 28.11.2017 / 11:57

1 answer

2

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

In SQL you create TYPE:

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

In Vb you use a DataTable to pass this information to the procedure:

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 procedure 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

Because I do not know 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: It works only for the SqlCommand

    
01.08.2018 / 20:38