1

I want to make this query set based and remove the while loop for inserting.

I am passing in a TVP to the stored procedure with the type and quantity required. Because TVP needs to be READONLY, I am copying to a temp table to do the delete in the while loop. I have control over the way the data is passed in so could change the TVP if required.

CREATE TYPE WidgetsUDT AS TABLE(
    [WidgetTypeId] [int] NOT NULL,
    [Required] [int] NOT NULL
)
GO
CREATE TABLE WidgetOrder (Id int, WidgetTypeId int, OrderId int)
GO
CREATE PROCEDURE [BuyWidgets]
@Widgets [WidgetsUDT] READONLY
AS
BEGIN
SET NOCOUNT ON
DECLARE @WidgetTypeId int, @Required int
CREATE TABLE #TempWidgets (WidgetTypeId int, Required int)
INSERT INTO #TempWidgets SELECT * FROM @Widgets

WHILE EXISTS(SELECT * FROM #TempWidgets)
BEGIN
SELECT TOP 1 @Required = Required, @WidgetTypeId = WidgetTypeId FROM         #TempWidgets

;WITH CTE AS 
( 
SELECT TOP (@Required) *
FROM WidgetOrder
 WHERE OrderId IS NULL AND
 WidgetTypeId = @WidgetTypeId
) 
UPDATE CTE SET OrderId = 100

DELETE FROM #TempWidgets WHERE WidgetTypeId = @WidgetTypeId
END

DROP TABLE #TempWidgets

    SELECT * FROM WidgetOrder
END    
GO

INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (1,1,NULL) 
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (2,1,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (3,1,1)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (4,2,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (5,2,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (6,2,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (7,2,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (8,2,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (9,2,2)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (10,3,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (11,3,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (12,3,NULL)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (13,3,3)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (14,3,3)
INSERT WidgetOrder (Id, WidgetTypeId, OrderId) VALUES (15,3,3)

DECLARE @MyWidgets WidgetsUDT
INSERT @MyWidgets (WidgetTypeId, Required) VALUES (1,2)
INSERT @MyWidgets (WidgetTypeId, Required) VALUES (2,3)
INSERT @MyWidgets (WidgetTypeId, Required) VALUES (3,1)
EXEC BuyWidgets @MyWidgets

DROP TABLE WidgetOrder
DROP PROCEDURE [BuyWidgets]
GO
DROP TYPE WidgetsUDT
GO
  • Hello, based on the version you have, you could maybe use the MERGE statement – Jefferson B. Elias Nov 18 '15 at 11:26
  • Thanks, I'll have a look. I'm testing on SQL 2008 but production is SQL Azure (2014) – Paul McCowat Nov 18 '15 at 11:34
  • Then you should be able to use this command. – Jefferson B. Elias Nov 18 '15 at 11:35
  • 1
    This doesn't need a while loop, a delete, a temp table, or a merge statement. – Aaron Bertrand Nov 18 '15 at 12:02
  • I think I have managed it with a cross apply and TOP (Required) – Paul McCowat Nov 18 '15 at 12:37
1

I believe I have solved it with the following cte and cross apply. Thanks to Aaron for letting me think it was simpler than I had it.

;WITH cte AS
(
SELECT 
    D.Id, D.WidgetTypeId
FROM 
    @Widgets R 
    CROSS APPLY 
    (
        SELECT 
            TOP(R.Required) *
        FROM 
            WidgetOrder A
        WHERE 
        A.OrderId IS NULL AND A.WidgetTypeId = r.WidgetTypeId) D
)
UPDATE A SET OrderId = 100 FROM WidgetOrder A JOIN cte ON A.Id = cte.Id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.