CREATE PROC GetMessages
@fromid int,
@toid int,
@searchtext varchar(100)
should work
HTH
-----Original Message-----
From: Allan Cliff - CFUG Spain [mailto:[EMAIL PROTECTED]
Sent: Friday, February 20, 2004 10:25
To: CF-Talk
Subject: Stored Procedures
I am trying to move a query to a Stored Procedure.
I want to add a LIKE to it but doesn't seem to work.
It seems to find the first letter only and then if it finds that it works
Example:
Message Body: "My name is Allan"
Search Text: "a" - Found 1 result
Search Text: "b" - Found 0 results
Search Text: "xxx" - Found 0 results
Search Text: "Allan" - Found 1 result
Search Text: "Alllan" - Found 1 result
Search Text: "asakjhfsiuhieqworg" - Found 1 result
Help Please. I know its Friday. And it really is Friday today.
Allan
-------------------------------------------------
CREATE PROC GetMessages
@fromid int,
@toid int,
@searchtext varchar
AS
DECLARE @query varchar(1000)
SET @query = 'SELECT * FROM Messages M '
SET @query = @query + ' WHERE 1=1 '
IF @fromid > 0
SET @query = @query + 'AND M.FromID = ' + convert(varchar,@fromid)
IF @toaccountid > 0
SET @query = @query + 'AND M.ToID = ' + convert(varchar,@toid)
-----------------------------------------------
**THIS SYNTAX DOESN'T WORK**
IF @searchtext <> ''
SET @query = @query + 'AND M.Message LIKE ' + "'%" + @searchtext +
"%'"
-----------------------------------------------
SET @query = @query + ' ORDER BY M.MessageID'
EXEC(@query)
GO
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]