Create Search using Stored Proc.

[Affleck]

Hi again I need more help please,

I am creating a search page to search a courses database the search form has 5 fields on it a Keyword Text field
and 4 checkboxes.

I want the stored procedure to generate a recordset based upon the search criteria set, but I dont want the user to have to enter criteria in each field, i.e if nothing is entered then all records should be returned.

I have this so far >>>

CREATE PROCEDURE spCourseSearch


@keywords varchar (255) = '', -from textarea field
@undergrad int = 0, - from checkbox on form
@postgrad int = 0, - from checkbox on form
@parttime int = 0, - from checkbox on form
@fulltime int = 0 - from checkbox on form
AS

declare @SQL varchar (2000)

SELECT @SQL = 'SELECT DISTINCT
Courses.CertificationID, Courses.CourseName,
Courses.CourseID, Certification.Certification,
CourseLevel.Leveltype, Mode.Mode
FROM Courses INNER JOIN
Certification ON
Courses.CertificationID = Certification.CertificationID INNER JOIN
CourseLevel ON
Courses.LevelID = CourseLevel.LevelID INNER JOIN
Mode ON Courses.ModeID = Mode.ModeID INNER JOIN
Keywords ON
Courses.CourseID = Keywords.CourseID
WHERE (Courses.CourseName IS NOT NULL)'

IF @keywords not like ' ' select @SQL = @SQL + ' AND Keywords.Keyword IN (' + @keywords + ')'


execute(@SQL)

This doesnt work I get an error>>

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'dgdbv'.

This is just using the keywords as criteria, I also want to filter records by Mode and Level.

Please can somebody help me???
Thanks in advance

 

[tlbroadbent]

The values in @keywords must be delimited by quotes (single ' or double &quot. For example, @keywords could contain ('accounting','calculus','literature') but not (accounting,calculus,literature). If you are using double quotes ("accounting","calculus","literature&quot then you may need to execute "SET quoted_identifier off" at the start of your SP. Terry

http://members.home.net/tlbroadbent/prog.htm

Neither success nor failure is ever final. -Roger Babson