Re: [cftalk] RE: Stored Procedures

@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

RE: [cftalk] RE: Stored Procedures

You are missing sinle quotes after the like I think. Use what you did first. -Original Message- From: Allan Cliff - CFUG Spain [mailto:[EMAIL PROTECTED] Sent: vrijdag 20 februari 2004 11:57 To: CF-Talk Subject: Re: [cftalk] RE: Stored Procedures I have simplified

RE: [cftalk] RE: Stored Procedures

: [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

RE: [cftalk] RE: Stored Procedures

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

Re: [cftalk] RE: Stored Procedures

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

RE: [cftalk] RE: Stored Procedures

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. This is true, but if you use stored procedures exclusively for all database access, you may be able to tighten