LIKE in dynamic queries

Question

I want to use the like keyword in a dynamic parameterized query. I want to protect my query from SQL injections so I don't want to pass the value, instead I want to pass my criteria while executing the query,

Is there a way I can do this?

SELECT 
  ComposeMail.ID,
  ComposeMail.DateTime, 
  ComposeMail.Subject, 
  ComposeMail.CreatedBy, 
  ComposeMail.ReceiverStatus,
  Users.Name,
  ROW_NUMBER() OVER(ORDER BY '+ @p_SortExpression +') AS Indexing
FROM 
  ComposeMail 
INNER JOIN
  Users
ON
  ComposeMail.CreatedBy = Users.ID
WHERE 
  (ToReceipientID=@p)
  AND (
    ReceiverStatus=3 
    OR ReceiverStatus=4
  )
  AND (
    (Subject Like ''%' + @p3 + '%'') 
    OR (Body Like ''%' + @p3 + '%'') 
    OR (Name Like ''%' + @p3 + '%'')
  )

This is my dynamic query string. I don't want to pass the value here.

Answer 1

To prevent against injection in a dynamic query you always want to do something like this (instead of doing ' + @var + ' in your example)

DECLARE @query nvarchar(2000),
        @paramList nvarchar(2000)

SET @query = 'SELECT * FROM dbo.Orders WHERE custLastName LIKE ''%'' + @custLastName + ''%'''
SET @paramList = '@custLastName varchar(30)'

EXEC SP_EXECUTESQL @query, @paramList, @custLastName

edit: example updated to use LIKE