sql server - Using variable in SQL LIKE statement

Question

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?

Answer 1

If you are using a Stored Procedure:

ALTER PROCEDURE <Name>
(
    @PartialName VARCHAR(50) = NULL
)

SELECT Name 
    FROM <table>
    WHERE Name LIKE '%' + @PartialName + '%'