Wednesday, May 16, 2007

Example of looping through query results AND conditional statements (dupe checking)

-- Get pubs related to program 11 and 29
DECLARE @pub_id int
DECLARE @dupe_count int
DECLARE pub_cur CURSOR
FOR select publication_id from tbl_Programs_To_Publications where program_id in (11,29)
OPEN pub_cur
FETCH pub_cur INTO @pub_id
-- loop through pubs
WHILE(@@FETCH_STATUS=0) BEGIN -- Here's a WHILE loop
-- check for existing xref
set @dupe_count = (
select count(program_publication_id)
from tbl_programs_to_publications
where program_id = 58 and publication_id = @pub_id
)
if @dupe_count = 0
-- insert xref for LAI
INSERT INTO tbl_Programs_To_Publications
(
program_id,
publication_id,
creation_user,
creation_date
)
VALUES (
58,
@pub_id,
115,
5/18/07
)

-- get the next record
FETCH pub_cur INTO @pub_id
END
CLOSE pub_cur
DEALLOCATE pub_cur

0 Comments:

Post a Comment

<< Home