Problem with LIKE %

I have a stored procedure:
CREATE PROCEDURE SearchHistoryClient
@.TextContain nvarchar(1000),
@.Email nvarchar(512),
@.Operator_URI nvarchar(512),
@.DateFrom DateTime,
@.DateTo DateTime,
@.Company_ID int
AS
DECLARE @.SQL nvarchar(4000)
SELECT @.SQL = '
SELECT
tblTemp.ChatTime,
tblTemp.Client_ID,
tblTemp.Client_SIPURI,
tblOperator.Operator_ID,
tblOperator.Operator_SIPURI,
tblOperator.Operator_Alias,
tblTemp.ChatTranscript
FROM
(SELECT * FROM tblSupportClient INNER JOIN tblClient ON
(tblSupportClient.ClientID = tblClient.Client_ID))
AS tblTemp INNER JOIN tblOperator ON tblTemp.Operator_ID =
tblOperator.Operator_ID
WHERE
(tblOperator.Company_ID = ''' + cast(@.Company_ID as nvarchar) + ''' ) AND
(tblTemp.ChatTime BETWEEN ''' + cast(@.DateFrom as nvarchar) + ''' AND
''' + cast(@.DateTo as nvarchar) + ''')
'
SELECT @.TextContain = '%'+ @.TextContain + '%';
-- Check if keyword is specified
IF (@.TextContain <> "")
BEGIN
SELECT @.SQL = @.SQL + ' AND (tblTemp.ChatTranscript LIKE ''' +
cast(@.TextContain as nvarchar) + ''') '
END
...
EXEC(@.SQL)
It always return 0 rows if @.TextContain contains more than a word.
e.g. if you call the procedure and the input string for @.TextContain is
"Hello"
then it returns some rows if they contains "Hello" string. But when you
specify "Hello John" it won't work correctly, though in your database the
string Hello John... exists.
Can anyone help me?Can you do a PRINT of @.SQL before executing, and see how the string is
formed? Btw, why are you using dynamic SQL?
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"John C#" <John C#@.discussions.microsoft.com> wrote in message
news:66468268-7264-41A7-AD86-F6162639EA6F@.microsoft.com...
> I have a stored procedure:
> CREATE PROCEDURE SearchHistoryClient
> @.TextContain nvarchar(1000),
> @.Email nvarchar(512),
> @.Operator_URI nvarchar(512),
> @.DateFrom DateTime,
> @.DateTo DateTime,
> @.Company_ID int
> AS
> DECLARE @.SQL nvarchar(4000)
> SELECT @.SQL = '
> SELECT
> tblTemp.ChatTime,
> tblTemp.Client_ID,
> tblTemp.Client_SIPURI,
> tblOperator.Operator_ID,
> tblOperator.Operator_SIPURI,
> tblOperator.Operator_Alias,
> tblTemp.ChatTranscript
> FROM
> (SELECT * FROM tblSupportClient INNER JOIN tblClient ON
> (tblSupportClient.ClientID = tblClient.Client_ID))
> AS tblTemp INNER JOIN tblOperator ON tblTemp.Operator_ID =
> tblOperator.Operator_ID
> WHERE
> (tblOperator.Company_ID = ''' + cast(@.Company_ID as nvarchar) + ''' ) AND
> (tblTemp.ChatTime BETWEEN ''' + cast(@.DateFrom as nvarchar) + ''' AND
> ''' + cast(@.DateTo as nvarchar) + ''')
> '
> SELECT @.TextContain = '%'+ @.TextContain + '%';
> -- Check if keyword is specified
> IF (@.TextContain <> "")
> BEGIN
> SELECT @.SQL = @.SQL + ' AND (tblTemp.ChatTranscript LIKE ''' +
> cast(@.TextContain as nvarchar) + ''') '
> END
> ...
> EXEC(@.SQL)
> --
> It always return 0 rows if @.TextContain contains more than a word.
> e.g. if you call the procedure and the input string for @.TextContain is
> "Hello"
> then it returns some rows if they contains "Hello" string. But when you
> specify "Hello John" it won't work correctly, though in your database the
> string Hello John... exists.
> Can anyone help me?|||Hi,
I tried to create a new table with a field of ntext type and then I use SQL
command to select, it works. However, it still does not work with fields
marked <long text>. Is it about type error? What is the problem here?
"Narayana Vyas Kondreddi" wrote:

> Can you do a PRINT of @.SQL before executing, and see how the string is
> formed? Btw, why are you using dynamic SQL?
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "John C#" <John C#@.discussions.microsoft.com> wrote in message
> news:66468268-7264-41A7-AD86-F6162639EA6F@.microsoft.com...
>
>