February 23, 2007 at 5:32 pm
I have 2 proc. one with cursor and one without it.
I am sure the functionallity has not been changed but the actual developer who wrote with cursor is stating that if we use the one without cursor it will not have produce the same result. Can someone verify ?
----------------------------------------------------------------------------
ALTER PROCEDURE update_qti_res_bbmd_dtmod_new(@pk1 numeric)
AS
SET NOCOUNT ON
DECLARE @newdate datetime
SELECT @newdate=bbmd_date_modified
FROM qti_result_data WHERE pk1=@pk1;
UPDATE qrd
SET bbmd_date_modified = @newdate
FROM qti_result_data qrd
JOIN x_qti_result_data xqrd
ON qrd.pk1 = xqrd.ancestor_pk1
----------------------------------------------------------------------------
----------------------------------------------------------------------------
ALTER PROCEDURE update_qti_res_bbmd_dtmod(
@pk1 numeric
)
AS
DECLARE @newdate datetime;
BEGIN
SELECT @newdate=bbmd_date_modified
FROM qti_result_data
WHERE pk1=@pk1;
DECLARE x_cursor CURSOR LOCAL FOR
SELECT ancestor_pk1 FROM x_qti_result_data WHERE descendant_pk1=@pk1;
DECLARE @anc_pk1 INTEGER;
OPEN x_cursor;
FETCH x_cursor INTO @anc_pk1;
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE qti_result_data
SET bbmd_date_modified = @newdate
WHERE pk1 = @anc_pk1;
FETCH x_cursor INTO @anc_pk1;
END;
CLOSE x_cursor;
DEALLOCATE x_cursor;
END;
----------------------------------------------------------------------------
Amit Lohia
February 26, 2007 at 8:00 am
This was removed by the editor as SPAM
February 26, 2007 at 7:29 pm
OK, I did a little review by commenting each query and this is what I came up with
-- based on the pk supplied, get the new date modified
SELECT
@newdate = bbmd_date_modified
FROM
qti_result_data
WHERE
pk1 = @pk1
-- update the date modified
-- where the pk supplied matches the descendant pk
-- and the ancestor pk matches the table pk
UPDATE
qrd
SET
bbmd_date_modified = @newdate
FROM
qti_result_data qrd
JOIN
x_qti_result_data xqrd
ON
qrd.pk1 = xqrd.ancestor_pk1
AND
descendant_pk1 = @pk1
DECLARE
@newdate datetime;
BEGIN
-- based on the pk supplied, get the new date modified
SELECT
@newdate = bbmd_date_modified
FROM
qti_result_data
WHERE
pk1 = @pk1
-- For every ancestor pk,
-- where the descendant pk matches the pk supplied
DECLARE
x_cursor CURSOR LOCAL FOR
SELECT ancestor_pk1 FROM x_qti_result_data WHERE descendant_pk1=@pk1;
DECLARE
@anc_pk1 INTEGER;
OPEN
x_cursor;
FETCH
x_cursor INTO @anc_pk1;
WHILE
@@FETCH_STATUS = 0
BEGIN
-- Update the date modified date
-- where the ancestor pk matches the table pk
UPDATE qti_result_data
SET bbmd_date_modified = @newdate
WHERE pk1 = @anc_pk1;
FETCH x_cursor INTO @anc_pk1;
END;
CLOSE
x_cursor;
DEALLOCATE
x_cursor;
So, if we pick out all the green bits for comparison
-- SP1 - no cursor
-- based on the pk supplied, get the new date modified
-- update the date modified
-- where the pk supplied matches the descendant pk
-- and the ancestor pk matches the table pk
-- SP2 - with cursor
-- based on the pk supplied, get the new date modified
-- For every ancestor pk,
-- where the descendant pk matches the pk supplied
-- Update the date modified date
-- where the ancestor pk matches the table pk
In my opinion, both stored procs are doing the same. In my opinion, you could also alter the query in the stored proc with no cursor to look like this
UPDATE
qrd
SET
bbmd_date_modified = qrd1.bbmd_date_modified
FROM
qti_result_data qrd
JOIN
x_qti_result_data xqrd
ON
qrd.pk1 = xqrd.ancestor_pk1
JOIN
qti_result_data qrd1
ON
qrd1.pk1 = descendant_pk1
WHERE
qrd1.pk1 = @pk1
The change here just joins you back to the original table to reference the date modified value
February 26, 2007 at 10:17 pm
Steve
You are right, I wanted to do the change without using the variable.
but at present I am having difficulty explain the developer with this simple join. I will freak him out with the third join.
Amit
Amit Lohia
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply