Using variable in SQL LIKE statement

Question

Hey everyone!

I've got a sproc (MSSQL 2k5) that will take a variable for a LIKE claus like so:

DECLARE @SearchLetter2 char(1)

SET @SearchLetter = 't'
SET @SearchLetter2 = @SearchLetter + '%'

SELECT *
    FROM BrandNames 
    WHERE [Name] LIKE @SearchLetter2 and IsVisible = 1 
    --WHERE [Name] LIKE 't%' and IsVisible = 1 
    ORDER BY [Name]

Unfortunately, the line currently running throws a syntax error, while the commented where clause runs just fine. Can anyone help me get the un-commented line working?

Thanks in advance!

--Joel

Answer 1

Joel is it that @SearchLetter hasn't been declared yet? Also the length of @SearchLetter2 isn't long enough for 't%'. Try a varchar of a longer length.

Answer 2

DECLARE @SearchLetter2 char(1)

Set this to a longer char.

Answer 3

This works for me on the Northwind sample DB, note that SearchLetter has 2 characters to it and SearchLetter also has to be declared for this to run:

declare @SearchLetter2 char(2)
declare @SearchLetter char(1)
Set @SearchLetter = 'A'
Set @SearchLetter2 = @SearchLetter+'%'
select * from Customers where ContactName like @SearchLetter2 and Region='WY'

Answer 4

I'm an idiot. :-P

You guys rule!