How can we implement "while loop" logic in the job ?
I am familiar with implementing looping in transformer but not quite sure how to go about implementing the looping for the below while loop inside the stored procedure:
Code: Select all
DECLARE @pnum int
SELECT @pnum = 0
DECLARE @vendor_id varchar(2000)
DECLARE @vendor_pnum varchar(2000)
DECLARE @vendor_list varchar(2000)
DECLARE @vendor_multi_list varchar(2000)
DECLARE @vendor_cost varchar(2000)
DECLARE @vendor_id_temp varchar(2000)
DECLARE @PART_NUM INT
DECLARE @VENDOR_ID VARCHAR(2000)
DECLARE @VENDOR_PART_NUMBER VARCHAR(2000)
DECLARE @LIST VARCHAR(2000)
DECLARE @COST VARCHAR(2000)
DECLARE cPARTS_MASTER INSENSITIVE CURSOR
FOR
SELECT PART_NUMBER, VENDOR_ID, VENDOR_PART_NUMBER, CAST(CONVERT(NUMERIC (19,0), LIST *100) AS VARCHAR(200)) AS LIST, CAST(CONVERT(NUMERIC(19,0),COST*100) AS VARCHAR(200)) AS COST
FROM MASTER_SUPPLIER
ORDER BY PART_NUMBER, VENDOR_ID
FOR READ ONLY
OPEN cPARTS_MASTER
FETCH FROM cPARTS_MASTER INTO @PART_NUM, @VENDOR_ID, @VENDOR_PART_NUMBER, @LIST, @COST
SELECT @pnum = @PART_NUM
WHILE
BEGIN
IF @pnum = @PART_NUM
BEGIN
IF @vendor_id = ''
SELECT @vendor_id = @VENDOR_ID, @vendor_pnum = RTRIM(LTRIM(@VENDOR_PART_NUMBER)), @vendor_list = @LIST, @vendor_cost = @COST, @vendor_multi_list = @LIST
ELSE
BEGIN
IF @VENDOR_ID = '500004'
SELECT @vendor_id = @vendor_id+CHAR(253)+@VENDOR_ID, @vendor_pnum = RTRIM(LTRIM(@vendor_pnum))+CHAR(253)+RTRIM(LTRIM(@VENDOR_PART_NUMBER)), @vendor_list = @LIST,
@vendor_cost = @vendor_cost+CHAR(253)+@COST, @vendor_multi_list = @vendor_multi_list+CHAR(253)+@LIST
ELSE SELECT @vendor_id = @vendor_id+CHAR(253)+@VENDOR_ID, @vendor_pnum = RTRIM(LTRIM(@vendor_pnum))+CHAR(253)+RTRIM(LTRIM(@VENDOR_PART_NUMBER)), @vendor_list = 0,
@vendor_cost = @vendor_cost+CHAR(253)+@COST, @vendor_multi_list = @vendor_multi_list+CHAR(253)+@LIST
END
FETCH NEXT FROM cPARTS_MASTER INTO @PART_NUM, @VENDOR_ID, @VENDOR_PART_NUMBER, @LIST, @COST
END
ELSE
BEGIN
INSERT INTO dbo.SUPPLIER_TEMP
(PART_NUMBER, VENDOR_ID, VENDOR_PART_NUMBER, LIST, COST, MULTI_LIST)
VALUES (@pnum, @vendor_id, @vendor_pnum, @vendor_list, @vendor_cost, @vendor_multi_list)
SELECT @pnum = @PART_NUM
SELECT @vendor_id = ''
END
END
To begin with I have sourced the data from the initial select statement and stored the @pnum in a stage variable and used it in a stage variable to check for the PART_NUM but was not quite sure in how to use a loop variable from here on to implement the rest of the logic.
Any input is appreciated.
Thanks !!