LIKE Condition while using parameters

[MaliciousMatt]

Hi All,

I'm trying to create a search stored procedure within MSSQL.

I've come up with the following:

ALTER PROCEDURE [dbo].[usp_Search]
@SearchText nchar(15)
AS
BEGIN
SELECT *
FROM         dbo.Person
WHERE FirstName LIKE '%'+@SearchText+'%'
END

I've tested this a few times on the test data I have in the database but it does not seem to be working correctly, although when I manually put something in, ie - take the parameter out and type in for example: WHERE FirstName LIKE '%at%' it will work...

Can anyone explain to me what I am doing incorrectly?

[MaliciousMatt]

I just realised this is in the MySQL threads...oops.

It's MS SQL.

[zswanson]

Hey Matt,

Try changing @SearchText to nvarchar(15) instead of nchar(15). Since it is char, it is padding the value with spaces so that it is 15 characters in total length. Therefore, when you are searching for 'at' you are really searching for '%at___________ __%' (where _ is a space).

Zach

[MaliciousMatt]

Hi Zach,

I think your onto something there - sounds right.

I'll give it a shot tomorrow and tell you how I go.

Thanks heaps - it's amazing how much I have just learnt if that's all it was.

Cheers,
Matt.

[MaliciousMatt]

Zach,
Helped heaps - working great now.

Thanks so much,

Matt.