Stored procedure problem URGENT

Question

Post reply

Stored procedure problem URGENT

eirk tsomik

Ten Centuries

Points: 1032
More actions
July 26, 2007 at 2:25 pm

#153712

I have a stored procedure that supposed to retrieve a name, division and so on. THe stored procedure seems to be executed but in the log file I am getting an error Incorrect syntax near 'DIVISION'. I need this urgent. Please help
CREATE PROCEDURE dbo.qryCourseNumbers @AY varchar(6), @dept varchar(20), @dept2 varchar(20) AS
delete from tmpCourseNumbers
insert into tmpCourseNumbers
select distinct c.courseid, left(c.coursenumber,3)+'.'+right(c.coursenumber,3)+' '+replace(c.coursetitle, " ' ", " '' ") as CourseTitle, v.division
from courseschedule.dbo.tblcourseinformation c inner join courseschedule.dbo.vw_courses_and_departments v
on c.courseid = v.courseid
where c.academicyear=@AY and (v.division=@dept or v.division=@dept2) and c.includeincatalog=-1 and (substring(c.coursenumber,4,1) <> '1' and substring(c.coursenumber,4,1) <> '2')
select * from tmpCourseNumbers
order by coursename
GO
Kindest Regards,
Web programmer

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

I'm not positive, but I believe you need to change this:

c.includeincatalog=-1

to

c.includeincatalog='-1'

Without the single quotes, it will treat the - sign as a subtraction.

-SQLBill

John Rowan SSC Guru Points: 56440 More actions · Answer 2

What happens if you run the SELECT portion of the SP manually through Query Analyzer?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

eirk tsomik Ten Centuries Points: 1032 More actions · Answer 3

If I run it through the query analizer I get the error saying that " ' " and " '' " columns does not exists, but if I do not use replace function it works fine

Kindest Regards,

Web programmer

John Rowan SSC Guru Points: 56440 More actions · Answer 4

Either run SET QUOTED_IDENTIFIER OFF and re-create your SP, or change your REPLACE statement to look like this:

replace(c.coursetitle, char(39), (char(39) + char(39)))

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Sergiy SSC Guru Points: 110209 More actions · Answer 5

Wrong use of quotes.

Here's what you need to fix:

... left(c.coursenumber,3)+'.'+right(c.coursenumber,3)+' '+replace(c.coursetitle, ' '' ', ' '''' ') as CourseTitle, ...

And

TRUNCATE TABLE tmpCourseNumbers

is much more effective than

delete from tmpCourseNumbers

_____________
Code for TallyGenerator