CODEWITHSUNDEEP
sqlstored-procedures
user1889838
user1889838

Reputation: 343

SQL: SQL Like statement with parameter values not returning desired values

I am trying to implement search in asp.net web application where I query a table and match search term with some columns.

To implement a partial search I thought, I can use Like clause. So I write my sql procedure like this - 

CREATE PROCEDURE sproc_SEARCH
    @author char(200)
AS
    SELECT * FROM Table WHERE column LIKE '%' + @author + '%'

Above procedure should return some rows, but it doesn't

However, If i just run following sql statement it gives me desired rows

SELECT * FROM Table WHERE column LIKE '%bha%'

I don't understand why this is the case, can use some help.

Thanks

Upvotes: 0

Views: 337

Answers (2)

Lamak
Lamak

Reputation: 70648

It probably has to do with @author being a CHAR instead of VARCHAR, so you have trailing spaces. Either use VARCHAR:

CREATE PROCEDURE sproc_SEARCH
    @author varchar(200)
AS
    SELECT * FROM Table WHERE column LIKE '%' + @author + '%'

or trim your parameter:

CREATE PROCEDURE sproc_SEARCH
    @author char(200)
AS
    SELECT * FROM Table WHERE column LIKE '%' + RTRIM(LTRIM(@author)) + '%'

Upvotes: 4

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Try this:

@author varchar(200)

Upvotes: 5

Related Questions