Problem with stored procedure...

L

lidds

Well-known member
Joined
Nov 9, 2004
Messages
210
Could someone please help, I have been trying to spot the problem but I cant seem to see the problem.

Code: 
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
 
Quote varchars

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

Code: 
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:
 
If you are using a stored proc and only passing in a couple of parameters you shouldnt be needing to concatenate strings anyway.
Try something like
Code: 
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
 
Back
Top