sql-server-2008 - 用户定义表类型列表中的循环项

Question

我有一个简单的表，其中包含 2 列 CarId int、主键和 CarName、varchar。

我需要创建一个接受汽车列表的存储过程。如果带有 CarId 的汽车不存在，我想插入那辆车，如果已经存在，我想更新它。

我创建了一个用户定义的表类型 CarType:

CREATE TYPE dbo.CarType
AS TABLE
(
    CARID int null,
    CARNAME varchar(800) not null,
);

和存储过程InsertCars:

CREATE PROCEDURE dbo.InsertCars
    @Cars AS CarType READONLY
AS
    DECLARE @CarCount INT = 0;
    DECLARE @Counter INT = 0;
BEGIN
    SET @CarsCount = (SELECT COUNT(CarId) FROM @Cars);

    WHILE(@Counter < @CarsCount)    
        BEGIN TRY
            --how get item from list Cars?
            @CurrentCar = Cars(@Counter)

            IF EXISTS(SELECT 1 FROM Cars WHERE CarsId = CurrentCar.CarId)
                --if doesn’t exist insert
                BEGIN
                    INSERT INTO CARS(CARID, CARNAME)
                    SELECT * FROM @CurrentCar;
                END

             ELSE
                BEGIN
                    --if exist update
                END
             END

            SET @Counter= @Counter + 1; 
        END TRY

        BEGIN  CATCH
            Print (ERROR_MESSAGE());
        END CATCH
END

我不知道如何从汽车列表(存储过程中的参数 Cars)获取当前汽车循环。

或者这个问题的一些优雅的解决方案。

Answer 1

看来你可以在这里摆脱循环:

CREATE PROCEDURE dbo.InsertCars
    @Cars AS CarType READONLY
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE c
    SET c.CARNAME = c2.CARNAME
    FROM Cars c
        JOIN @Cars c2 on c2.CARID = c.CARID;

    INSERT INTO Cars(CARID, CARNAME)
    SELECT c.CARID, c.CARNAME
    FROM @Cars c
    WHERE NOT EXISTS (SELECT 1 FROM Cars WHERE CARID = c.CARID);

END

或(使用merge构造):

CREATE PROCEDURE dbo.InsertCars
    @Cars AS CarType READONLY
AS
BEGIN
    SET NOCOUNT ON;

    MERGE Cars AS target
    USING (SELECT CARID, CARNAME FROM @Cars) AS source (CARID, CARNAME)
    ON (target.CARID = source.CARID)
    WHEN MATCHED THEN 
        UPDATE SET CARNAME = source.CARNAME
    WHEN NOT MATCHED THEN   
        INSERT (CARID, CARNAME)
        VALUES (source.CARID, source.CARNAME);

END