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]
