必须在表类型上声明标量变量@insertValues

debugcn 发表于 Dev

香蕉面包

我有以下spr，并且在尝试创建时出现关于声明标量变量insertvalues的错误。我创建了一个类型，重定向。

CREATE TYPE Redirect AS TABLE(
    RED_ID INT,
    RED_Type int,
    RED_FromURL varchar(max),
    RED_ToURL varchar(max),
    RED_StartDate datetime,
    RED_EndDate datetime
);

下面是存储过程，它正面临问题。（使用它的更新@insertValues.RED_Type

   CREATE PROCEDURE [dbo].[AddUpdateRedirects]
    @insertValues Redirect READONLY
AS
BEGIN


    DECLARE @updateRowsAffected AS INT
    DECLARE @insertRowsAffected AS INT

    --Update
    UPDATE
        tbl_Redirects
    SET
        RED_Type = @insertValues.RED_Type,
        RED_FromURL = @insertValues.RED_FromURL,
        RED_ToURL = @insertValues.RED_ToUrl,
        RED_StartDate = @insertValues.RED_StartDate,
        RED_EndDate = @insertValues.RED_EndDate,
        RED_DateUpdated = GETDATE()
    FROM @insertValues
    WHERE tbl_Redirects.RED_ID = @insertValues.RED_ID
    AND @insertValues.RED_ID <> 0

    SET @updateRowsAffected = @@ROWCOUNT


    INSERT INTO
        tbl_Redirects
        (
            RED_Type,
            RED_DateCreated,
            RED_FromURL,
            RED_ToURL,
            RED_StartDate,
            RED_EndDate
        )
    SELECT  iv.RED_Type, 
            GETDATE(), 
            iv.RED_FromURL, 
            iv.RED_ToUrl, 
            iv.RED_StartDate, 
            iv.RED_EndDate 
    FROM @insertValues iv
    LEFT JOIN tbl_Redirects rd ON rd.RED_FromURL = iv.RED_FromURL
    WHERE iv.RED_ID = 0                 -- Where it's a new record
    AND rd.RED_ID IS NULL               -- and where the FromURL doesn't currently exist


    SET @insertRowsAffected = @@ROWCOUNT

    SELECT @updateRowsAffected + @insertRowsAffected as TotalRowsAffected

END

皮匠

您只需要将表名包装在方括号中：

UPDATE  tbl_Redirects
SET     RED_Type = [@insertValues].RED_Type ,
        RED_FromURL = [@insertValues].RED_FromURL ,
        RED_ToURL = [@insertValues].RED_ToUrl ,
        RED_StartDate = [@insertValues].RED_StartDate ,
        RED_EndDate = [@insertValues].RED_EndDate ,
        RED_DateUpdated = GETDATE()
FROM    @insertValues
WHERE   tbl_Redirects.RED_ID = [@insertValues].RED_ID
        AND [@insertValues].RED_ID <> 0

或如Zohar Peled州给它起的别名：

UPDATE  tbl_Redirects
SET     RED_Type = t.RED_Type ,
        RED_FromURL = t.RED_FromURL ,
        RED_ToURL = t.RED_ToUrl ,
        RED_StartDate = t.RED_StartDate ,
        RED_EndDate = t.RED_EndDate ,
        RED_DateUpdated = GETDATE()
FROM    @insertValues t
WHERE   tbl_Redirects.RED_ID = t.RED_ID
        AND t.RED_ID <> 0

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。