Re: [cftalk] RE: Stored Procedures

[Allan Cliff - CFUG Spain]

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]

---