Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

using variable in a cursor query in a stored procedure

Status
Not open for further replies.

Guest_imported

New member
Jan 1, 1970
0
Hey,

I've got a problem with using queries in a cursor. When I use no variables in my query, everything works just fine, but now I need to specify a variable, an ID which is given as input for my stored procedure. This is what works:

--------------

CREATE PROCEDURE sp_skill_match
@job_id INT
AS

DECLARE score_cursor CURSOR FAST_FORWARD FOR
SELECT link2.candidate_id AS candidate_id, ((COUNT(link2.candidate_id) + COUNT(lvl2.skill_level_id) + .0) / (2 * COUNT(link3.skill_detail_id))) AS score FROM skill_link AS link
INNER JOIN skill_link AS link2 ON link2.skill_detail_id = link.skill_detail_id AND link2.job_id IS NULL
INNER JOIN skill_level AS lvl ON lvl.skill_level_id = link.skill_level_id
LEFT OUTER JOIN skill_level AS lvl2 ON lvl2.skill_level_id = link2.skill_level_id AND lvl2.ordering >= lvl.ordering
FULL OUTER JOIN skill_link AS link3 ON link3.job_id = 2
WHERE link.job_id = 2
GROUP BY link2.candidate_id
ORDER BY link2.candidate_id ASC

DECLARE @candidate_id INT
DECLARE @score DECIMAL

OPEN score_cursor

FETCH NEXT FROM score_cursor
INTO @candidate_id, @score
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO candidate_results (candidate_id, job_id, score) VALUES (@candidate_id, @job_id, @score)
FETCH NEXT FROM score_cursor
INTO @candidate_id, @score
END

CLOSE score_cursor
DEALLOCATE score_cursor
GO

-----------------

this is what I need, but doesn't work (difference is in the first two lines of the last four lines of the first query (phew), 2 is substituted for @job_id, which is given 2 as input (verified)):

-------------

CREATE PROCEDURE sp_skill_match
@job_id INT
AS

DECLARE score_cursor CURSOR FAST_FORWARD FOR
SELECT link2.candidate_id AS candidate_id, ((COUNT(link2.candidate_id) + COUNT(lvl2.skill_level_id) + .0) / (2 * COUNT(link3.skill_detail_id))) AS score FROM skill_link AS link
INNER JOIN skill_link AS link2 ON link2.skill_detail_id = link.skill_detail_id AND link2.job_id IS NULL
INNER JOIN skill_level AS lvl ON lvl.skill_level_id = link.skill_level_id
LEFT OUTER JOIN skill_level AS lvl2 ON lvl2.skill_level_id = link2.skill_level_id AND lvl2.ordering >= lvl.ordering
FULL OUTER JOIN skill_link AS link3 ON link3.job_id = @job_id
WHERE link.job_id = @job_id
GROUP BY link2.candidate_id
ORDER BY link2.candidate_id ASC

DECLARE @candidate_id INT
DECLARE @score DECIMAL

OPEN score_cursor

FETCH NEXT FROM score_cursor
INTO @candidate_id, @score
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO candidate_results (candidate_id, job_id, score) VALUES (@candidate_id, @job_id, @score)
FETCH NEXT FROM score_cursor
INTO @candidate_id, @score
END

CLOSE score_cursor
DEALLOCATE score_cursor
GO

----------------

puhlease help!
 
I can't see anything obvious, and I'd have to agree that it looks like it should work.

this is what I need, but doesn't work

By "doesn't work", do you mean you get an error or you get no rows or you get too many rows? Robert Bradley
Do you have too much money? Visit
 
It should work fine...what error are you getting?

I'd think a better way to do this would be:
INSERT INTO candidate_results (candidate_id, job_id, score)
SELECT Link2.candidate_id, @Job_ID,
((COUNT(Link2.candidate_id) + COUNT(lvl2.skill_level_id) + .0) / (2 * COUNT(Link3.skill_detail_id)))
FROM (Insert From Clause)
WHERE link.job_id = @Job_ID
GROUP BY Link2.candidate_id

No cursor...only one statement.

Not to be negative or anything but...
Holy crap man, does that join actually work? I'd bet a dollar that if you were to implement some temp tables you'd get better performance than self joining a couple tables on themselves a couple times with a full join thrown in...
 
thanks for replying. That one-statement thing is a better way indeed. Still having a problem though, the same as with the original one. Seems like if I use parameters the stored procedure runs forever, if I use an integer instead of the parameter it finishes (correctly) within 1 second!

Now I've used STR(@job_id) everywhere in the query where I used @job_id alone. That does work.

I don't get it, but it does.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top