RE: [cftalk] RE: Stored Procedures

[Mike Townend]

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]

---