Problem with stored procedure...

[lidds]

Could someone please help, I have been trying to spot the problem but I can't seem to see the problem.

 

CREATE PROCEDURE [dbo].[spSearchComm] @project as varchar(50), @search as varchar(255)

AS

declare @strQuery varchar(8000)

declare @strOrig varchar(3)

set @strOrig = 'Yes'

set @strQuery = 'SELECT * FROM commentsTbl WHERE projName=' + @project + ' and original=' + @strOrig + ' and commtitle like ' + @search + ' or commDesc like ' + @search + ' or commAction like ' + @search

exec (@strQuery)

GO

[code]

 

It is giving the following error but I everything things to be fine:

 

Incorrect syntax near the keyword 'or'

 

Thanks

 

Simon

[MrPaul]

Quote varchars

 

The above create procedure query executes fine so I assume you're getting the error when you actually execute the procedure. Perhaps you should place quotes around the varchar parameters like so:

 

CREATE PROCEDURE [dbo].[spSearchComm] @project as varchar(50), @search as varchar(255)
AS
declare @strQuery varchar(8000)
declare @strOrig varchar(3)
set @strOrig = 'Yes'
set @strQuery = 'SELECT * FROM commentsTbl WHERE projName=''' + @project + ''' and original=''' + @strOrig + ''' and commtitle like ''' + @search + ''' or commDesc like ''' + @search + ''' or commAction like ''' + @search + ''''
exec (@strQuery)
GO

 

Good luck :cool:

[PlausiblyDamp]

If you are using a stored proc and only passing in a couple of parameters you shouldn't be needing to concatenate strings anyway.

Try something like

CREATE PROCEDURE [dbo].[spSearchComm] @project as varchar(50), @search as varchar(255)
AS
declare @strOrig varchar(3)
set @strOrig = 'Yes'
SELECT * FROM commentsTbl WHERE projName= @project  and original= @strOrig  and commtitle like @search or commDesc like @search  or commAction like @search

[lidds]

Thank you this solved my problem.