Operator LIKE does not work in my app vb.net


Greetings to all. I have the following problem I have a procedure stored in SQL Server that retrieves data with the like operator, but it does not work for me.

create procedure recupera_art 
@cadena char(40) 
select * from articulo where clave like '%'+ @cadena +'%'

change the procedure like this:

create procedure recupera_art 
    @cadena char(40) 
    select * from articulo where clave like @cadena 

and from my application (vb.net) I pass the parameter in this way:

cmd.Parameters.AddWithValue("@cadena", "%" + dts.gclave_art + "%")

but it works to me half, that is to say I have the following keys (AX101080, ..., AX101095), when wanting to make the query I do not do anything when putting A, AX, AX1 or AX10, I have to put AX101 so that I started to filter all the data in the table.

What I want is that by putting AX, A, AX1 or AX10 I can filter the data, I think that is possible.

I appreciate your help to solve the problem.

asked by Silvestre Silva 16.01.2018 в 22:08

2 answers


Thank you all for your cooperation, I leave the solution in case someone happens.

create procedure recupera_art 
@cadena varchar(40) 
select * from articulo where clave like '%'+ @cadena +'%'

In the application.

cmd.Parameters.AddWithValue("@cadena", dts.gclave_art)

The explanation of the problem is that a char(40) physically uses the whole space, this can be verified with this example:

DECLARE @cadena CHAR(40)
SELECT @cadena  = 'BUSCA'
PRINT   '*' + @cadena + '*'

The exit:

*BUSCA                                   *

When doing like '%'+ @cadena +'%' when @cadena is a CHAR we are actually doing:

like '%BUSCA                                   %'

Provoking that we can not find the desired information.

answered by 17.01.2018 / 19:05

If your procedure works correctly in SQL you could try to execute it as if it were t-sql

cmd.CommandText = "EXEC recupera_art 'texto para buscar'"
cmd.CommandType = CommandType.Text

Similarly, if you already have '%' in your procedure, you do not need to add it in your vb code, I would also recommend that you change that char (40) to varchar (max)

answered by 17.01.2018 в 18:55