asked
Cursor with inner while loop
Hi! 1. I have a list of parts and data that I fill a cursor with. 2. For each part number, I must select all WORKORDER_BASE_ID and WORKORDER_TYPE 3. For each WORKORDER_BASE_ID, I must select all WORKORDER_BASE_ID and WORKORDER_TYPE until there is no more WORKORDER_TYPE='M' 4. All parts must be entered into a table and include the 'BasePart' on each row. I started the following cursor but do not know how to create a inner loop that will continue and add to my table until there is no more WORKORDER_TYPE='M' DECLARE @PartNo nvarchar(255); DECLARE @Make nvarchar(255); DECLARE @Model nvarchar(255); DECLARE @AppType nvarchar(255); DECLARE @Description nvarchar(255); DECLARE @LicCode nvarchar(255); DECLARE @PMA nvarchar(255); DECLARE @HTS_CODE nvarchar(255); DECLARE @BasePart nvarchar(255); DECLARE @WORKORDER_TYPE char(1); DECLARE @WORKORDER_BASE_ID varchar(30); DECLARE PartAssemblycursor CURSOR FOR SELECT
P.ID AS PartNo, TA.Make, TA.Model, TA.AppType, TA.Description, TA.LicCode, TA.PMA, TA.HTS_CODE, TA.PartNo AS BasePart FROM TopAssembly07_31_2013 AS TA INNER JOIN PART AS P ON TA.PartNo = dbo.ParsePart(
P.ID, 1) AND dbo.ParsePart(
P.ID, 3) = 'FN' OPEN PartAssemblycursor FETCH NEXT FROM PartAssemblycursor into @PartNo, @Make, @Model, @AppType, @Description, @LicCode, @PMA, @HTS_CODE, @BasePart; WHILE @@FETCH_STATUS = 0 BEGIN SELECT [WORKORDER_TYPE] ,[WORKORDER_BASE_ID] FROM [ONTIC].[dbo].[REQUIREMENT] Where [PART_ID]=@PartNo and [WORKORDER_TYPE]='M' INSERT INTO [Apps].[dbo].[OEM_PartWhereUsed] ([PartNo] ,[Make] ,[Model] ,[AppType] ,[Description] ,[LicCode] ,[PMA] ,[HTS_CODE] ,[WORKORDER_TYPE] ,[WORKORDER_BASE_ID] ,[BasePart]) VALUES (@PartNo ,@Make ,@Model ,@AppType ,@Description ,@LicCode ,@PMA ,@HTS_CODE ,@WORKORDER_TYPE ,@WORKORDER_BASE_ID ,@BasePart) FETCH NEXT FROM PartAssemblycursor into @PartNo, @Make, @Model, @AppType, @Description, @LicCode, @PMA, @HTS_CODE, @BasePart; END CLOSE PartAssemblycursor; DEALLOCATE PartAssemblycursor;
The site runs on votes. For each answer below that was helpful, please mark them by clicking on the thumbs up next to those answers. If anyone one answer lead to a solution, mark it by clicking on the check mark next to it.
Do you need a cursor at all? I think you could just add a JOIN to your first SELECT and you would have all the data you need. The query will also run a lot faster done that way. So for example could you use something like this: INSERT INTO [Apps].[dbo].[OEM_PartWhereUsed] ([PartNo] ,[Make] ,[Model] ,[AppType] ,[Description] ,[LicCode] ,[PMA] ,[HTS_CODE] ,[WORKORDER_TYPE] ,[WORKORDER_BASE_ID] ,[BasePart]) SELECT
P.ID AS PartNo , TA.Make , TA.Model , TA.AppType , TA.Description , TA.LicCode , TA.PMA , TA.HTS_CODE , R.WORKORDER_TYPE , R.WORKORDER_BASE_ID , TA.PartNo AS BasePart FROM TopAssembly07_31_2013 AS TA INNER JOIN PART AS P ON TA.PartNo = dbo.ParsePart(
P.ID, 1) AND dbo.ParsePart(
P.ID, 3) = 'FN' INNER JOIN [ONTIC].[dbo].[REQUIREMENT] R ON
P.ID = R.PART_ID WHERE R.WORKORDER_TYPE = 'M'
Even if there is one or more master parts in the requirement table the JOIN should still work. Do you get too many records with the query I posted? Not enough records? Unless you're doing something different to each input row - which I don't think you are - you shouldn't need a cursor.
Thank you for Responding mjharper. I am using a cursor because the part that comes from TopAssembly07_31_2013 will have one or more master parts in the Requirement table. Each part the comes from the requirement table will also have one or more master parts. This is why I need to loop each part through the requirement table until there is no more WORKORDER_TYPE = 'M'. I may need to create a temp table within the loop in the cursor as well so that I can store the master parts.
Very simply put the structure for properly nested cursors can be: DECLARE curOut1 CURSOR FAST_FORWARD READ_ONLY FOR SELECT statement OPEN curOut1 FETCH NEXT FROM curOut1 INTO @... WHILE @@FETCH_STATUS = 0 BEGIN -- Do Something before the inner cursor DECLARE curIn2 CURSOR FAST_FORWARD READ_ONLY FOR SELECT statement OPEN curIn2 FETCH NEXT FROM curIn2 INTO @... WHILE @@FETCH_STATUS = 0 BEGIN -- Do Something FETCH NEXT FROM curIn2 INTO @... END CLOSE curIn2 DEALLOCATE curIn2 -- Do Your Inserts / Updates here FETCH NEXT FROM curOut1 INTO @... END CLOSE curOut1 DEALLOCATE curOut1 This form of cursor is the fastest form and hits the CPU the least. You can't use it if you need to update the cursor for your process. I got used to these when I graduated from SQL-7 to 2000.
17 People are following this question.
