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