IF @searchtext <> ''
SET @query = @query + ' AND M.Message LIKE ''' + '%' + @searchtext + '%'''
was the solution. Single Quotes Only.
Thanks
Allan
----- Original Message -----
From: Mike Townend
To: CF-Talk
Sent: Friday, February 20, 2004 12:03 PM
Subject: RE: [cftalk] RE: Stored Procedures
try,
SET @query = @query + ' AND Message LIKE ' + "'%" + @searchtext +
"%'"
basically you are missing the quote around the like
-----Original Message-----
From: Allan Cliff - CFUG Spain [mailto:[EMAIL PROTECTED]
Sent: Friday, February 20, 2004 10:57
To: CF-Talk
Subject: Re: [cftalk] RE: Stored Procedures
I have simplified this to:
But get error [Macromedia][SQLServer JDBC Driver][SQLServer]Line 1:
Incorrect syntax near 'a'.
Any more help please?
Allan
--------------------------------------------------
CREATE PROC GetMessages2
@searchtext varchar (100)
AS
DECLARE @query varchar(1000)
SET @query = 'SELECT * FROM Messages'
SET @query = @query + ' WHERE 1=1'
IF @searchtext <> ''
SET @query = @query + 'AND Message LIKE' + '%' + @searchtext + '%'
EXEC(@query)
GO
----- Original Message -----
From: Mike Townend
To: CF-Talk
Sent: Friday, February 20, 2004 11:43 AM
Subject: [cftalk] RE: Stored Procedures
try adding a size to the varchar
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]
- Re: mySQL date ? Thomas Chiverton
- Re: mySQL date ? Stephen Moretti
- Re: mySQL date ? Jochem van Dieten
- Re: mySQL date ? Stephen Moretti
- Re: mySQL date ? cf
- Re: mySQL date ? Thomas Chiverton
- Stored Procedures Allan Cliff - CFUG Spain
- RE: Stored Procedures Mike Townend
- Re: [cftalk] RE: Stored Pro... Allan Cliff - CFUG Spain
- RE: [cftalk] RE: Stored... Mike Townend
- Re: mySQL date ? Allan Cliff - CFUG Spain
- Re: mySQL date ? cf
- Re: mySQL date ? cf
- Re: mySQL date ? Stephen Moretti
- Re: mySQL date ? cf
- Re: mySQL date ? Stephen Moretti
- Re: mySQL date ? cf
- Re: mySQL date ? Stephen Moretti
- RE: mySQL date ? C. Hatton Humphrey
- Re: mySQL date ? SOLVED cf
- Re: mySQL date ? Oliver Tupman