I'm not sure there is much point in using a stored proc to build a SQL
string and execute it, you'll get no speed/efficiency benefit at all, in
fact it will probably be slower.


Having said that, try this..


CREATE PROC GetMessages2
@searchtext varchar (100)
AS
DECLARE @query varchar(1000)
SET @query = 'SELECT * FROM Messages WHERE 1=1'
IF len(@searchtext) > 0 BEGIN
SET @query = @query + 'AND Message LIKE'  + '%' + @searchtext + '%'
END
EXEC(@query)
GO

-----Original Message-----
From: Allan Cliff - CFUG Spain [mailto:[EMAIL PROTECTED]
Sent: 20 February 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]

Reply via email to