sql - 必须声明标量变量 "@OUTSTANDINGSDATA"

Question

我在 SQL SP 中有这段代码。

当我测试@OUTSTANDINGSDATA 是否不为空时，我收到“必须声明标量变量“@OUTSTANDINGSDATA””错误。
其他变量不会发生这种情况。
有任何想法吗？
提前谢谢

USE [PantaRei]
GO
/****** Object:  StoredProcedure [dbo].[InsertDdrsData]    Script Date: 08/22/2012 14:29:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[InsertDdrsData] 
@RECEIVABLESDATA RECEIVABLESTABLE READONLY,
@DILUTIONSDATA DILUTIONSTABLE READONLY,
@ACCOUNTABLESDATA ACCOUNTABLESTABLE READONLY,
@OUTSTANDINGSDATA OUTSTANDINGSTABLE READONLY,
@TABLE1 VARCHAR(MAX),
@TABLE2 VARCHAR(MAX),
@TABLE3 VARCHAR(MAX),
@TABLE4 VARCHAR(MAX)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    DECLARE @CMD nvarchar(345)

    SET NOCOUNT ON

    SET @TABLE1 = RTRIM(@TABLE1)
    SET @TABLE2 = RTRIM(@TABLE2)
    SET @TABLE3 = RTRIM(@TABLE3)

    SELECT * INTO #t1 FROM @RECEIVABLESDATA

    SET @CMD =

        'INSERT INTO ' + QUOTENAME(@TABLE1) +
        'SELECT * FROM #t1'

    EXECUTE sp_executesql @CMD

    SELECT * INTO #t2 FROM @DILUTIONSDATA

    SET @CMD =

        'INSERT INTO ' + QUOTENAME(@TABLE2) +
        'SELECT * FROM #t2'

    EXECUTE sp_executesql @CMD

    SELECT * INTO #t3 FROM @ACCOUNTABLESDATA

    SET @CMD =

        'INSERT INTO ' + QUOTENAME(@TABLE3) +
        'SELECT * FROM #t3'

    EXECUTE sp_executesql @CMD

    IF @OUTSTANDINGSDATA IS NOT NULL
        BEGIN
            SELECT * INTO #t4 FROM @OUTSTANDINGSDATA

            SET @CMD =

                'INSERT INTO ' + QUOTENAME(@TABLE4) +
                'SELECT * FROM #t4'

            EXECUTE sp_executesql @CMD

        END 
END

Answer 1

@OUTSTANDINGSDATA是一个表值参数。

不能是NULL即使没有通过。改为测试一个空表。

IF NOT EXISTS (SELECT * FROM @OUTSTANDINGSDATA)