i want to get records using like keyword

Question

Hi friends!!

I have the following query which is working fine, but I also want to get records using the like keyword. My query is as follows,

USE [POBox]
GO
/****** Object:  StoredProcedure [dbo].[test]    Script Date: 07/06/2009 12:55:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[test] --'DateTime','1','10'
(
@p_SortExpression NVARCHAR(100),
@p_StartIndex INT,
@p_MaxRows INT
)
AS
SET NOCOUNT ON;
IF LEN(@p_SortExpression)= 0 
SET @p_SortExpression = 'DateTime DESC'

DECLARE @Sql NVARCHAR(4000)
SET @Sql ='SELECT ID, DateTime, Subject, CreatedBy, ToReceipientID , ReceiverStatus
           FROM
           (
           SELECT ID, DateTime, Subject,CreatedBy, ToReceipientID, ReceiverStatus,
           ROW_NUMBER() OVER(ORDER BY ' + @p_SortExpression + ') AS Indexing
           FROM ComposeMail
            WHERE (Subject Like '%+ test +%') 
           )
           AS NewDataTable
           WHERE  Indexing > '+ CONVERT(NVARCHAR(10), @p_StartIndex) + ' AND Indexing<=(' + CONVERT (NVARCHAR(10),@p_StartIndex )+'+'+ CONVERT(NVARCHAR(10),@p_MaxRows)+')'

           EXEC sp_executesql @sql

Can anybody help? How do I use like and I also want to use it as a parameter.

Answer 1

Don't you already have like used in:

WHERE (Subject Like '%+ test +%')

Not sure what is the question or if I did not understand it completely.

Answer 2

I think what your asking for is: How do I use the like command in sql?

For your bit of code, add a new parameter:

@SearchText nvarchar(100)

then replace:

WHERE (Subject Like '%+ test +%')

with

WHERE Subject (Like ''%'+ ISNULL(test, '') +'%''')

For more information look here: http://msdn.microsoft.com/en-us/library/ms179859.aspx