SQL Statement (Easy one!) 2

[Affleck]

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!)

 

[rajeevnandanmishra]

Hi there,
The procedure you created is seems ok. Yeh you don't have to use '+' if you are not adding any parameters in the sql statement.

By the way what error are you getting.

And try this also. for time being you can write
SELECT @sql
before exec(@sql)
It will give you the exact sql statement which is being executed. You can try to run it and this will show exactly where is the problem.

 

[elmo29]

Whats the error? + is usally put in to make things more readable.

if you try adding
print @sql

before you excute it, you will be able to see what sql statement you are running ... sometimes it can be something daft like your string is not large enough and has been truncated .... since your sql statement is pretty large that could be the case ... it looks over 1000 to me.

 

[Affleck]

Thanks very much, see I said it would be simple Id just declared @SQL as 1000 varchar, ive changed it to 2000 and it seems to work so far

thanks again!!!