how to loop through a list of ID's in SQL Server-CodePudding

I have a list of ids that i need to loop through, and use in an insert statement for example

I have built the table here:

DECLARE @ProductIds TABLE (ID nvarchar(1000))

INSERT INTO @ProductIds (ID)
    SELECT ProductTypeId FROM ProductType

DECLARE MY_CURSOR CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR 
    SELECT DISTINCT Id 
    FROM @ProductIds

OPEN MY_CURSOR

FETCH NEXT FROM MY_CURSOR INTO @ProductIds

WHILE @@FETCH_STATUS = 0
BEGIN 
    --each so basically each productId is used in the @ProductId parameter below
    INSERT INTO myProducts (ProductTypeId, prodDesc, Value) 
    VALUES (@ProductId, 'CA1BBFC3-35EA-4984-BCD1-9E0EB385E4BE', 0)

    PRINT @ProductIds

    FETCH NEXT FROM MY_CURSOR INTO @ProductIds
END

CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR

I'm not sure if the problem is the syntax or how I've tried to do it but I keep getting errors

Msg 137, Level 16, State 1, Line 76
Must declare the scalar variable "@ProductIds".

Msg 137, Level 16, State 1, Line 81
Must declare the scalar variable "@ProductIds".

Msg 137, Level 16, State 1, Line 82
Must declare the scalar variable "@ProductIds".

I'm learning SQL so I'm finding it quite a challenge at the moment

CodePudding user response：

If you are learning SQL the first thing you should do is stop thinking about looping anything, SQL is optimized to operate on sets not one row at a time. You just need:

insert dbo.myProducts (ProductTypeId, prodDesc,Value) 
  Select ProductTypeId,'CA1BBFC3-35EA-4984-BCD1-9E0EB385E4BE',0 
  from dbo.ProductType;

Though this seems a little messed up, there looks like some confusion between a ProductId and a ProductTypeId, and it seems like you want to insert one product with the same GUID description for every product type. But that's the general structure to avoid 70 lines of code and an inefficient loop.