sql - 动态 SQL - 参数化查询

Question

我使用带参数的动态sql并且有问题:)

在代码块中它可以工作，但是当我调用过程时出现错误，我需要声明标量变量@name_dummy

所以：

DECLARE @START_TIME DATETIME = GETDATE()

DECLARE @TMP_NAMES TABLE (
       SCHEMA_N VARCHAR(500),
       TABLE_N VARCHAR(500),
       CONDITION VARCHAR(MAX),
       TARGETDB VARCHAR(500),
       SOURCEDB VARCHAR(500),
       COLUMN_LIST VARCHAR(MAX)
       )
DECLARE @SCHEMA_N VARCHAR(500)
DECLARE @TABLE_N VARCHAR(500)
DECLARE @CONDITION VARCHAR(MAX)
DECLARE @COLUMN_LIST VARCHAR(MAX)
DECLARE @TARGETDB VARCHAR(500)
DECLARE @SOURCEDB VARCHAR(500)

INSERT INTO @TMP_NAMES
       SELECT
             o.SCHEMA_N
          ,o.TABLE_N
          ,o.Condition
          ,o.TargetDB
          ,o.SourceDB
          ,STUFF((SELECT
                           ', ' + c.name
                    FROM sys.columns c
                    WHERE c.object_id = o.object_id
                    AND c.system_type_id <> 189 /* Pomijam timestamp*/
                    ORDER BY c.column_id
                    FOR XML PATH (''))
             , 1, 1, '')
       FROM Table_A o
       ORDER BY 1,
       2

SELECT
       SCHEMA_N
   ,TABLE_N
   ,CONDITION
   ,TARGETDB
   ,SOURCEDB
   ,COLUMN_LIST
FROM @TMP_NAMES

DECLARE KUR CURSOR
FOR
SELECT
       SCHEMA_N
   ,TABLE_N
   ,CONDITION
   ,TARGETDB
   ,SOURCEDB
   ,COLUMN_LIST
FROM @TMP_NAMES

OPEN KUR

FETCH NEXT
FROM KUR
INTO @SCHEMA_N,
       @TABLE_N,
       @CONDITION,
       @TARGETDB,
       @SOURCEDB,
       @COLUMN_LIST

WHILE @@FETCH_STATUS = 0
BEGIN
       DECLARE @TOP VARCHAR(100)
       SET @TOP = ( SELECT CAST(Wartosc AS VARCHAR) FROM Table_B WHERE Id = 3 )
       IF @TOP < 40000
             SET @TOP = 40000

       WHILE ( 1 = 1 )
       BEGIN
           DECLARE @CHECK BIT = 0
           DECLARE @sqlCMD NVARCHAR(MAX) = 'SELECT TOP 1 @check_exists = 1 FROM ' 
              + @SOURCEDB + '.' + @SCHEMA_N + '.' + @TABLE_N + ' WHERE ' + @CONDITION 

           exec sp_executesql @sqlCMD, N'@check_exists bit OUTPUT, @START_TIME DATE', @check_exists = @CHECK OUTPUT, @START_TIME = @START_TIME;  

           SELECT @sqlCMD , @CHECK
       END
END

FETCH NEXT
FROM KUR
INTO @SCHEMA_N,
    @TABLE_N,
    @CONDITION,
    @TARGETDB,
    @SOURCEDB,
    @COLUMN_LIST

CLOSE KUR;
DEALLOCATE KUR

当我执行它时，它正在编译和工作（返回值），例如我想要执行的 sql。

SELECT TOP 1 @check_exists = 1 FROM MSSF15_WarstwaPosrednia.KatalogZdarzen.MSSF_RaportZdarzenPLKB2CS2K WHERE  StatusPrzetwarzania = 4  AND DataWpisu < DATEADD(MONTH, -3, @START_TIME)

参数在这里工作（@check_exists 是输出和 @START_TIME 条件参数），但是当我在程序中执行这个时我得到了错误

必须声明标量变量“@START_TIME”。137

SP定义：

   USE xxxx
/****** Object:  StoredProcedure dbo.dummy   Script Date: 06/09/2018 13:40:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE dbo.dummy
AS
BEGIN
    /*

    PRZENIESIENIE DANYCH

*/
    DECLARE @START_TIME DATETIME = GETDATE()
    DECLARE @TMP_NAMES TABLE (
        SCHEMA_N VARCHAR(500),
        TABLE_N VARCHAR(500),
        CONDITION VARCHAR(MAX),
        TARGETDB VARCHAR(500),
        SOURCEDB VARCHAR(500),
        COLUMN_LIST VARCHAR(MAX)
        )
    DECLARE @SCHEMA_N VARCHAR(500)
    DECLARE @TABLE_N VARCHAR(500)
    DECLARE @CONDITION VARCHAR(MAX)
    DECLARE @COLUMN_LIST VARCHAR(MAX)
    DECLARE @TARGETDB VARCHAR(500)
    DECLARE @SOURCEDB VARCHAR(500)

    INSERT INTO @TMP_NAMES
    SELECT o.SCHEMA_N,
        o.TABLE_N,
        o.Condition,
        o.TargetDB,
        o.SourceDB,
        STUFF((
                SELECT ', ' + c.NAME
                FROM sys.columns c
                WHERE c.object_id = o.object_id
                    AND c.system_type_id <> 189 /* Pomijam timestamp*/
                ORDER BY c.column_id
                FOR XML PATH('')
                ), 1, 1, '')
    FROM dbo.Table_A o
    ORDER BY 1,
        2

    SELECT SCHEMA_N,
        TABLE_N,
        CONDITION,
        TARGETDB,
        SOURCEDB,
        COLUMN_LIST
    FROM @TMP_NAMES

    DECLARE KUR CURSOR
    FOR
    SELECT SCHEMA_N,
        TABLE_N,
        CONDITION,
        TARGETDB,
        SOURCEDB,
        COLUMN_LIST
    FROM @TMP_NAMES

    OPEN KUR

    FETCH NEXT
    FROM KUR
    INTO @SCHEMA_N,
        @TABLE_N,
        @CONDITION,
        @TARGETDB,
        @SOURCEDB,
        @COLUMN_LIST

    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @TOP VARCHAR(100)

        SET @TOP = (
                SELECT CAST(Wartosc AS VARCHAR)
                FROM dbo.Table_B
                WHERE Id = 3
                )

        IF @TOP < 40000
            SET @TOP = 40000

        WHILE (1 = 1)
        BEGIN
            BEGIN TRY
                BEGIN TRANSACTION T_TEST

                DECLARE @CHECK BIT = 0
                DECLARE @START_TIME_P DATE = GETDATE()
                DECLARE @sqlCMD NVARCHAR(MAX) = 'SELECT TOP 1 @check_exists = 1 FROM ' + @SOURCEDB + '.' + @SCHEMA_N + '.' + @TABLE_N + ' WHERE ' + @CONDITION

                SELECT @CHECK,
                    @START_TIME_P,
                    @sqlCMD

                EXEC sp_executesql @sqlCMD,
                    N'@check_exists bit OUTPUT, @START_TIME DATE',
                    @check_exists = @CHECK OUTPUT,
                    @START_TIME = @START_TIME_P;

                IF OBJECT_ID('tempdb..#TMP_ID') IS NOT NULL
                    DROP TABLE #TMP_ID

                CREATE TABLE #TMP_ID (Id INT)

                EXEC (
                        'INSERT INTO #TMP_ID
                   SELECT TOP ' + @TOP + ' Id
                   FROM ' + @SOURCEDB + '.' + @SCHEMA_N + '.' + @TABLE_N + ' WHERE ' + @CONDITION
                        )

                DECLARE @sqlCMD_Ins NVARCHAR(MAX)

                SET @sqlCMD_Ins = 'INSERT INTO ' + @TARGETDB + '.' + @SCHEMA_N + '.' + @TABLE_N + ' '
                SET @sqlCMD_Ins = @sqlCMD_Ins + 'SELECT ' + @COLUMN_LIST + ' FROM ' + @SOURCEDB + '.' + @SCHEMA_N + '.' + @TABLE_N + ' WHERE Id IN ( SELECT Id FROM #TMP_ID )'

                IF @sqlCMD_Ins IS NULL
                    RAISERROR (
                            'Brak polecenia insert @sqlCMD_Ins',
                            16,
                            1
                            )

                EXEC (@sqlCMD_Ins)

                DECLARE @sqlCMD_Del NVARCHAR(MAX)

                SET @sqlCMD_Del = 'DELETE FROM ' + @SOURCEDB + '.' + @SCHEMA_N + '.' + @TABLE_N + ' WHERE Id IN ( SELECT Id FROM #TMP_ID )'

                IF @sqlCMD_Del IS NULL
                    RAISERROR (
                            'Brak polecenia insert @sqlCMD_Ins',
                            16,
                            1
                            )

                EXEC (@sqlCMD_Del)

                DROP TABLE #TMP_ID

                COMMIT TRANSACTION T_TEST
            END TRY

            BEGIN CATCH
                IF (@@TRANCOUNT > 0)
                BEGIN
                    ROLLBACK TRANSACTION T_TEST

                    PRINT 'Wycofanie zmian'
                    PRINT ERROR_MESSAGE() + '  ' + CAST(ERROR_NUMBER() AS VARCHAR(256))
                END
            END CATCH

            IF @CHECK = 0
                BREAK
        END

        EXEC ('SELECT ''' + @SOURCEDB + '.' + @SCHEMA_N + '.' + @TABLE_N + ''' AS DB ,COUNT(1) AS ILOSC FROM ' + @SOURCEDB + '.' + @SCHEMA_N + '.' + @TABLE_N + ' UNION ALL ' + 'SELECT ''' + @TARGETDB + '.' + @SCHEMA_N + '.' + @TABLE_N + ''' AS DB ,COUNT(1) AS ILOSC FROM ' + @TARGETDB + '.' + @SCHEMA_N + '.' + @TABLE_N)

        FETCH NEXT
        FROM KUR
        INTO @SCHEMA_N,
            @TABLE_N,
            @CONDITION,
            @TARGETDB,
            @SOURCEDB,
            @COLUMN_LIST
    END

    CLOSE KUR;

    DEALLOCATE KUR;

    DECLARE @END_TIME DATETIME = GETDATE()

    SELECT 'Daty przetworzenia',
        @START_TIME_P AS 'START',
        @END_TIME AS 'END'

    SELECT 'Czas (min)',
        DATEDIFF(MINUTE, @START_TIME_P, @END_TIME)
END

伙计们，也许你有类似的情况，可以提供帮助。

错误信息

Wycofanie zmian
Must declare the scalar variable "@START_TIME".  137

谢谢

标签： sqlsql-servertsql

Answer 1

代替

exec sp_executesql @sqlCMD, N'@check_exists bit OUTPUT, @START_TIME DATE', @check_exists = @CHECK OUTPUT, @START_TIME = @START_TIME;

尝试

declare @date_in DATE = GETDATE()
exec sp_executesql @sqlCMD, N'@check_exists bit OUTPUT, @START_TIME DATE', @check_exists = @CHECK OUTPUT, @START_TIME = @date_in;