Can anyone tell me why when forming an SQL statement in a stored procedure you have to use the ' + ' and concatenate?
eg: I want to create a stored proc that will be used to generate an ASP Recordset from search parameters.
Is this OK? (It doesnt seem to work) or should I split the @SQL statement with ' + ' as Ive seen done in examples?
>>CREATE PROCEDURE spCourseSearch
@keywords varchar (255),
@undergrad int = 0,
@postgrad int = 0,
@parttime int = 0,
@fulltime int = 0
AS
declare @SQL nvarchar (1000)
SELECT @SQL = 'SELECT Distinct Courses.CourseName,Courses.UCAScode, Courses.Duration, Courses.CourseInfo, Courses.CourseWork, Courses.TransferOpps, Courses.Accreditation, Courses.Selection, Courses.Careers, Courses.Telephone, Courses.email, Courses.Fax, Courses.CompulsoryReq, Courses.StandardEntry, Courses.GCE_VCE, Courses.EdExcel, Courses.ScotHigher, Courses.IrishHigher, Courses.HEFC, Courses.other, Courses.AdditionalNotes, Certification.Certification, EntryReq.EntryReq, Schools.SchoolName, Schools.WebLink, Location.Location, Faculty.FacultyName, Faculty.Weblink, Mode.Mode, Units.Content, Units.YearID, Courses.CourseID, Units.CourseID AS unitcourseID, keywords.CourseID AS keywordCourseID FROM Courses INNER JOIN keywords ON Courses.CourseID = keywords.CourseID INNER JOIN Location ON Courses.LocationID = Location.LocationID INNER JOIN Mode ON Courses.ModeID = Mode.ModeID INNER JOIN Schools ON Courses.SchoolID = Schools.SchoolID INNER JOIN Units ON Courses.CourseID = Units.CourseID INNER JOIN Certification ON Courses.CertificationID = Certification.CertificationID INNER JOIN EntryReq ON Courses.EntryReqID = EntryReq.EntryReqID INNER JOIN Faculty ON Schools.FacultyID = Faculty.FacultyID WHERE Courses.CourseName is not null'
IF @keywords IS NOT NULL select @SQL = @SQL + ' AND keywords.keyword in (' + @keywords + ')'
IF @parttime > 0 select @SQL = @SQL + ' AND Mode.ModeID in (' + @parttime + ')'
IF @fulltime > 0 select @SQL = @SQL + ' AND Mode.ModeID in (' + @fulltime + ')'
execute(@SQL)
Thanks in ADvance for any help - Im a web designer really but Im having to learn all this!! Please be patient!)
eg: I want to create a stored proc that will be used to generate an ASP Recordset from search parameters.
Is this OK? (It doesnt seem to work) or should I split the @SQL statement with ' + ' as Ive seen done in examples?
>>CREATE PROCEDURE spCourseSearch
@keywords varchar (255),
@undergrad int = 0,
@postgrad int = 0,
@parttime int = 0,
@fulltime int = 0
AS
declare @SQL nvarchar (1000)
SELECT @SQL = 'SELECT Distinct Courses.CourseName,Courses.UCAScode, Courses.Duration, Courses.CourseInfo, Courses.CourseWork, Courses.TransferOpps, Courses.Accreditation, Courses.Selection, Courses.Careers, Courses.Telephone, Courses.email, Courses.Fax, Courses.CompulsoryReq, Courses.StandardEntry, Courses.GCE_VCE, Courses.EdExcel, Courses.ScotHigher, Courses.IrishHigher, Courses.HEFC, Courses.other, Courses.AdditionalNotes, Certification.Certification, EntryReq.EntryReq, Schools.SchoolName, Schools.WebLink, Location.Location, Faculty.FacultyName, Faculty.Weblink, Mode.Mode, Units.Content, Units.YearID, Courses.CourseID, Units.CourseID AS unitcourseID, keywords.CourseID AS keywordCourseID FROM Courses INNER JOIN keywords ON Courses.CourseID = keywords.CourseID INNER JOIN Location ON Courses.LocationID = Location.LocationID INNER JOIN Mode ON Courses.ModeID = Mode.ModeID INNER JOIN Schools ON Courses.SchoolID = Schools.SchoolID INNER JOIN Units ON Courses.CourseID = Units.CourseID INNER JOIN Certification ON Courses.CertificationID = Certification.CertificationID INNER JOIN EntryReq ON Courses.EntryReqID = EntryReq.EntryReqID INNER JOIN Faculty ON Schools.FacultyID = Faculty.FacultyID WHERE Courses.CourseName is not null'
IF @keywords IS NOT NULL select @SQL = @SQL + ' AND keywords.keyword in (' + @keywords + ')'
IF @parttime > 0 select @SQL = @SQL + ' AND Mode.ModeID in (' + @parttime + ')'
IF @fulltime > 0 select @SQL = @SQL + ' AND Mode.ModeID in (' + @fulltime + ')'
execute(@SQL)
Thanks in ADvance for any help - Im a web designer really but Im having to learn all this!! Please be patient!)