Is it possible to tell the size of the VARCHAR in a NamedParameter?

6

I'm working on a tuning of a SQL Server database. Through the query below, I can see how the bank ran some of my heaviest queries.

SELECT TOP 30 deqt.TEXT AS Query,
              last_execution_time
FROM   sys.dm_exec_query_stats deqs
       CROSS APPLY sys.Dm_exec_sql_text(deqs.sql_handle) deqt
       CROSS APPLY sys.Dm_exec_query_plan(deqs.plan_handle) deqp
WHERE  ( deqt.TEXT LIKE '%SELECT AV.ID,%'
         OR deqt.TEXT LIKE '%SELECT ''DEPARTMENT'' AS NODE,%'
         OR deqt.TEXT LIKE '%SELECT COUNT(P.COD) FROM _PEDIDOS P%WHERE P.CONCLUIDO = 1  AND P.COD_LOJA in ( @P0 ) AND P.STATUS_PARCEIRO =  @P1%' )
         --1 = 1
       AND deqt.TEXT NOT LIKE 'select top 30%'
       AND last_execution_time > Getdate() - 1
ORDER  BY 1

The problem with the DBA is that the input parameter of the query was of a different type than the column. So I changed the Java code to the following:

   final MapSqlParameterSource params = new MapSqlParameterSource()
    params.addValue('SKU', sku, Types.VARCHAR)
    params.addValue('ORGANIZATION_ID', organizationId, Types.BIGINT)

    List<AttributeValue> attributeValues = jdbcTemplate.query(SELECT_ATTRIBUTE_VALUES, params, { ResultSet rs, int idx ->

However, VARCHAR (8000) arrives, and the column is 255. Is there any way to report this? does anyone know?

    
asked by anonymous 01.11.2017 / 13:14

1 answer

0

I solved it by casting it in SQL.

  

WHERE field = CAST (: FIELD AS VARCHAR (255)

    
28.11.2017 / 18:57