Проблемы с передачей переменных в хранимую процедуру как тип sql_variant

Question

Проблемы с передачей переменных в хранимую процедуру как тип sql_variant

У меня возникают проблемы при попытке передать переменные разных типов в хранимую процедуру, которая получает переменные в виде SQL_Variants.

Вот хранимая процедура, которую я вызываю:

CREATE PROCEDURE [dbo].[usp_GetOrCreateCorrespondenceBatchID]
    @CorrespondenceBatchName VARCHAR(100)
    , @CorrespondenceTypeId int
    , @CorrespondenceBatchId INT OUTPUT
    , @isNewlyCreatedBatch BIT OUTPUT
    , @ParameterName01 NVARCHAR(100) = null, @ParamenterValue01 SQL_VARIANT = null
    , @ParameterName02 NVARCHAR(100) = null, @ParamenterValue02 SQL_VARIANT = null
    , @ParameterName03 NVARCHAR(100) = null, @ParamenterValue03 SQL_VARIANT = null
    , @ParameterName04 NVARCHAR(100) = null, @ParamenterValue04 SQL_VARIANT = null
    , @ParameterName05 NVARCHAR(100) = null, @ParamenterValue05 SQL_VARIANT = null
    , @ParameterName06 NVARCHAR(100) = null, @ParamenterValue06 SQL_VARIANT = null
    , @ParameterName07 NVARCHAR(100) = null, @ParamenterValue07 SQL_VARIANT = null
    , @ParameterName08 NVARCHAR(100) = null, @ParamenterValue08 SQL_VARIANT = null
    , @ParameterName09 NVARCHAR(100) = null, @ParamenterValue09 SQL_VARIANT = null
    , @ParameterName10 NVARCHAR(100) = null, @ParamenterValue10 SQL_VARIANT = null
AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
        SET NOCOUNT ON;

        SET @isNewlyCreatedBatch = 0
        SET @CorrespondenceBatchId = NULL;

        SELECT  @CorrespondenceBatchId = CorrespondenceBatch.CorrespondenceBatchID
            FROM    dbo.CorrespondenceBatch
            WHERE   CorrespondenceBatch.[Name] = @CorrespondenceBatchName

        IF @CorrespondenceBatchId IS NULL
        BEGIN
            SET @isNewlyCreatedBatch = 1
            INSERT INTO dbo.CorrespondenceBatch
                    ( CreatedBy
                    , CreatedDate
                    , LastModifiedBy
                    , LastModifiedDate
                    , CorrespondenceTypeID
                    , [Name]
                    )
            VALUES  ( SUSER_SNAME()  -- CreatedBy - nvarchar(50)
                    , GETUTCDATE()  -- CreatedDate - datetime
                    , SUSER_SNAME()  -- LastModifiedBy - nvarchar(50)
                    , GETUTCDATE()  -- LastModifiedDate - datetime
                    , @CorrespondenceTypeId
                    , @CorrespondenceBatchName  -- Name - nvarchar(100)
                    )
            SET @CorrespondenceBatchId = SCOPE_IDENTITY() 
            IF @ParameterName01 IS NOT NULL
            BEGIN 
                 IF SQL_VARIANT_PROPERTY(@ParamenterValue01, 'BaseType') IN ('datetime', 'smalldatetime', 'datetime2', 'date', 'time')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueDateTime )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName01, CONVERT(VARCHAR(MAX),@ParamenterValue01, 121), CONVERT(DATETIME, @ParamenterValue01))
                 END
                 ELSE IF SQL_VARIANT_PROPERTY(@ParamenterValue01, 'BaseType') IN ('bigint', 'int', 'smallint', 'tinyint', 'bit')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueBigInt )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName01, CONVERT(VARCHAR(MAX),@ParamenterValue01), CONVERT(BIGINT, @ParamenterValue01))
                 END
                 ELSE IF SQL_VARIANT_PROPERTY(@ParamenterValue01, 'BaseType') IN ('decimal', 'money', 'smallmoney')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueDecimal )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName01, CONVERT(VARCHAR(MAX),@ParamenterValue01), CONVERT(DECIMAL(18,6), @ParamenterValue01))
                 END
                 ELSE
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString)VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName01, CONVERT(VARCHAR(MAX),@ParamenterValue01))
                 END
            END
            IF @ParameterName02 IS NOT NULL
            BEGIN 
                 IF SQL_VARIANT_PROPERTY(@ParamenterValue02, 'BaseType') IN ('datetime', 'smalldatetime', 'datetime2', 'date', 'time')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueDateTime )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName02, CONVERT(VARCHAR(MAX),@ParamenterValue02, 121), CONVERT(DATETIME, @ParamenterValue02))
                 END
                 ELSE IF SQL_VARIANT_PROPERTY(@ParamenterValue02, 'BaseType') IN ('bigint', 'int', 'smallint', 'tinyint', 'bit')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueBigInt )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName02, CONVERT(VARCHAR(MAX),@ParamenterValue02), CONVERT(BIGINT, @ParamenterValue02))
                 END
                 ELSE IF SQL_VARIANT_PROPERTY(@ParamenterValue02, 'BaseType') IN ('decimal', 'money', 'smallmoney')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueDecimal )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName02, CONVERT(VARCHAR(MAX),@ParamenterValue02), CONVERT(DECIMAL(18,6), @ParamenterValue02))
                 END
                 ELSE
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString)VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName02, CONVERT(VARCHAR(MAX),@ParamenterValue02))
                 END
            END
            IF @ParameterName03 IS NOT NULL
            BEGIN 
                 IF SQL_VARIANT_PROPERTY(@ParamenterValue03, 'BaseType') IN ('datetime', 'smalldatetime', 'datetime2', 'date', 'time')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueDateTime )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName03, CONVERT(VARCHAR(MAX),@ParamenterValue03, 121), CONVERT(DATETIME, @ParamenterValue03))
                 END
                 ELSE IF SQL_VARIANT_PROPERTY(@ParamenterValue03, 'BaseType') IN ('bigint', 'int', 'smallint', 'tinyint', 'bit')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueBigInt )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName03, CONVERT(VARCHAR(MAX),@ParamenterValue03), CONVERT(BIGINT, @ParamenterValue03))
                 END
                 ELSE IF SQL_VARIANT_PROPERTY(@ParamenterValue03, 'BaseType') IN ('decimal', 'money', 'smallmoney')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueDecimal )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName03, CONVERT(VARCHAR(MAX),@ParamenterValue03), CONVERT(DECIMAL(18,6), @ParamenterValue03))
                 END
                 ELSE
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString)VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName03, CONVERT(VARCHAR(MAX),@ParamenterValue03))
                 END
            END
            IF @ParameterName04 IS NOT NULL
            BEGIN 
                 IF SQL_VARIANT_PROPERTY(@ParamenterValue04, 'BaseType') IN ('datetime', 'smalldatetime', 'datetime2', 'date', 'time')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueDateTime )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName04, CONVERT(VARCHAR(MAX),@ParamenterValue04, 121), CONVERT(DATETIME, @ParamenterValue04))
                 END
                 ELSE IF SQL_VARIANT_PROPERTY(@ParamenterValue04, 'BaseType') IN ('bigint', 'int', 'smallint', 'tinyint', 'bit')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueBigInt )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName04, CONVERT(VARCHAR(MAX),@ParamenterValue04), CONVERT(BIGINT, @ParamenterValue04))
                 END
                 ELSE IF SQL_VARIANT_PROPERTY(@ParamenterValue04, 'BaseType') IN ('decimal', 'money', 'smallmoney')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueDecimal )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName04, CONVERT(VARCHAR(MAX),@ParamenterValue04), CONVERT(DECIMAL(18,6), @ParamenterValue04))
                 END
                 ELSE
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString)VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName04, CONVERT(VARCHAR(MAX),@ParamenterValue04))
                 END
            END
            IF @ParameterName05 IS NOT NULL
            BEGIN 
                 IF SQL_VARIANT_PROPERTY(@ParamenterValue05, 'BaseType') IN ('datetime', 'smalldatetime', 'datetime2', 'date', 'time')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueDateTime )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName05, CONVERT(VARCHAR(MAX),@ParamenterValue05, 121), CONVERT(DATETIME, @ParamenterValue05))
                 END
                 ELSE IF SQL_VARIANT_PROPERTY(@ParamenterValue05, 'BaseType') IN ('bigint', 'int', 'smallint', 'tinyint', 'bit')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueBigInt )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName05, CONVERT(VARCHAR(MAX),@ParamenterValue05), CONVERT(BIGINT, @ParamenterValue05))
                 END
                 ELSE IF SQL_VARIANT_PROPERTY(@ParamenterValue05, 'BaseType') IN ('decimal', 'money', 'smallmoney')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueDecimal )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName05, CONVERT(VARCHAR(MAX),@ParamenterValue05), CONVERT(DECIMAL(18,6), @ParamenterValue05))
                 END
                 ELSE
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString)VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName05, CONVERT(VARCHAR(MAX),@ParamenterValue05))
                 END
            END
            IF @ParameterName06 IS NOT NULL
            BEGIN 
                 IF SQL_VARIANT_PROPERTY(@ParamenterValue06, 'BaseType') IN ('datetime', 'smalldatetime', 'datetime2', 'date', 'time')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueDateTime )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName06, CONVERT(VARCHAR(MAX),@ParamenterValue06, 121), CONVERT(DATETIME, @ParamenterValue06))
                 END
                 ELSE IF SQL_VARIANT_PROPERTY(@ParamenterValue06, 'BaseType') IN ('bigint', 'int', 'smallint', 'tinyint', 'bit')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueBigInt )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName06, CONVERT(VARCHAR(MAX),@ParamenterValue06), CONVERT(BIGINT, @ParamenterValue06))
                 END
                 ELSE IF SQL_VARIANT_PROPERTY(@ParamenterValue06, 'BaseType') IN ('decimal', 'money', 'smallmoney')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueDecimal )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName06, CONVERT(VARCHAR(MAX),@ParamenterValue06), CONVERT(DECIMAL(18,6), @ParamenterValue06))
                 END
                 ELSE
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString)VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName06, CONVERT(VARCHAR(MAX),@ParamenterValue06))
                 END
            END
            IF @ParameterName07 IS NOT NULL
            BEGIN 
                 IF SQL_VARIANT_PROPERTY(@ParamenterValue07, 'BaseType') IN ('datetime', 'smalldatetime', 'datetime2', 'date', 'time')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueDateTime )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName07, CONVERT(VARCHAR(MAX),@ParamenterValue07, 121), CONVERT(DATETIME, @ParamenterValue07))
                 END
                 ELSE IF SQL_VARIANT_PROPERTY(@ParamenterValue07, 'BaseType') IN ('bigint', 'int', 'smallint', 'tinyint', 'bit')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueBigInt )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName07, CONVERT(VARCHAR(MAX),@ParamenterValue07), CONVERT(BIGINT, @ParamenterValue07))
                 END
                 ELSE IF SQL_VARIANT_PROPERTY(@ParamenterValue07, 'BaseType') IN ('decimal', 'money', 'smallmoney')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueDecimal )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName07, CONVERT(VARCHAR(MAX),@ParamenterValue07), CONVERT(DECIMAL(18,6), @ParamenterValue07))
                 END
                 ELSE
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString)VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName07, CONVERT(VARCHAR(MAX),@ParamenterValue07))
                 END
            END
            IF @ParameterName08 IS NOT NULL
            BEGIN 
                 IF SQL_VARIANT_PROPERTY(@ParamenterValue08, 'BaseType') IN ('datetime', 'smalldatetime', 'datetime2', 'date', 'time')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueDateTime )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName08, CONVERT(VARCHAR(MAX),@ParamenterValue08, 121), CONVERT(DATETIME, @ParamenterValue08))
                 END
                 ELSE IF SQL_VARIANT_PROPERTY(@ParamenterValue08, 'BaseType') IN ('bigint', 'int', 'smallint', 'tinyint', 'bit')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueBigInt )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName08, CONVERT(VARCHAR(MAX),@ParamenterValue08), CONVERT(BIGINT, @ParamenterValue08))
                 END
                 ELSE IF SQL_VARIANT_PROPERTY(@ParamenterValue08, 'BaseType') IN ('decimal', 'money', 'smallmoney')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueDecimal )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName08, CONVERT(VARCHAR(MAX),@ParamenterValue08), CONVERT(DECIMAL(18,6), @ParamenterValue08))
                 END
                 ELSE
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString)VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName08, CONVERT(VARCHAR(MAX),@ParamenterValue08))
                 END
            END
            IF @ParameterName09 IS NOT NULL
            BEGIN 
                 IF SQL_VARIANT_PROPERTY(@ParamenterValue09, 'BaseType') IN ('datetime', 'smalldatetime', 'datetime2', 'date', 'time')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueDateTime )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName09, CONVERT(VARCHAR(MAX),@ParamenterValue09, 121), CONVERT(DATETIME, @ParamenterValue09))
                 END
                 ELSE IF SQL_VARIANT_PROPERTY(@ParamenterValue09, 'BaseType') IN ('bigint', 'int', 'smallint', 'tinyint', 'bit')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueBigInt )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName09, CONVERT(VARCHAR(MAX),@ParamenterValue09), CONVERT(BIGINT, @ParamenterValue09))
                 END
                 ELSE IF SQL_VARIANT_PROPERTY(@ParamenterValue09, 'BaseType') IN ('decimal', 'money', 'smallmoney')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueDecimal )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName09, CONVERT(VARCHAR(MAX),@ParamenterValue09), CONVERT(DECIMAL(18,6), @ParamenterValue09))
                 END
                 ELSE
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString)VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName09, CONVERT(VARCHAR(MAX),@ParamenterValue09))
                 END
            END
            IF @ParameterName10 IS NOT NULL
            BEGIN 
                 IF SQL_VARIANT_PROPERTY(@ParamenterValue10, 'BaseType') IN ('datetime', 'smalldatetime', 'datetime2', 'date', 'time')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueDateTime )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName10, CONVERT(VARCHAR(MAX),@ParamenterValue10, 121), CONVERT(DATETIME, @ParamenterValue10))
                 END
                 ELSE IF SQL_VARIANT_PROPERTY(@ParamenterValue10, 'BaseType') IN ('bigint', 'int', 'smallint', 'tinyint', 'bit')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueBigInt )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName10, CONVERT(VARCHAR(MAX),@ParamenterValue10), CONVERT(BIGINT, @ParamenterValue10))
                 END
                 ELSE IF SQL_VARIANT_PROPERTY(@ParamenterValue10, 'BaseType') IN ('decimal', 'money', 'smallmoney')
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString, ValueDecimal )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName10, CONVERT(VARCHAR(MAX),@ParamenterValue10), CONVERT(DECIMAL(18,6), @ParamenterValue10))
                 END
                 ELSE
                 BEGIN
                    INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, Name, ValueString)VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName10, CONVERT(VARCHAR(MAX),@ParamenterValue10))
                 END
            END
        END
    END

Вот мой вызов этой хранимой процедуры из другой хранимой процедуры:

    EXEC dbo.usp_GetOrCreateCorrespondenceBatchID @CorrespondenceBatchName, @CorrespondenceTypeId, @CorrespondenceBatchId OUTPUT,
        @isNewlyCreatedBatch OUTPUT, N'StartDate', @StartDate, N'EndDate', @EndDate, N'PopulationID', @PopulationID

Когда я пытаюсь отладить этот вызов / хранимую процедуру, отладчик пропускает эту хранимую процедуру, и выходные параметры никогда не устанавливаются. Да, я уверен в своей процедуре отладки... пробовал как SSMS, так и VisualStudio 2015. Кажется, что оба переступают через этот вызов при отладке. Иногда этот вызов работает правильно, а usp_GetOrCreateCorrespondenceBatchID выполняется правильно. После нескольких дней попыток отследить проблему, я не могу найти отчетливый случай, когда вызов всегда работает, а не работает. Эта хранимая процедура вызывается из многих родительских хранимых процедур. Я не могу найти последовательности в вызове хранимых процедур, которые всегда работают, и тех, которые не работают. Иногда вызов хранимой процедуры A работает, и в большинстве случаев это не так.

Таблица параметров довольно проста:

CREATE TABLE [dbo].[CorrespondenceBatchParameter](
    [CorrespondenceBatchParameterID] [bigint] IDENTITY(1,1) NOT NULL,
    [CreatedBy] [nvarchar](50) NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [LastModifiedBy] [nvarchar](50) NOT NULL,
    [LastModifiedDate] [datetime] NOT NULL,
    [CorrespondenceBatchID] [int] NOT NULL,
    [Name] [nvarchar](100) NOT NULL,
    [ValueString] [nvarchar](max) NULL,
    [ValueBigInt] [bigint] NULL,
    [ValueDateTime] [datetime] NULL,
    [ValueDecimal] [numeric](18, 6) NULL,
 CONSTRAINT [PK_CorrespondenceBatchParameter] PRIMARY KEY CLUSTERED 
(
    [CorrespondenceBatchParameterID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Причиной передачи параметров в виде sql_variant является то, что разные пакеты будут иметь разные параметры разных типов, и гораздо проще выбрать правильное значение типа позже без необходимости приведения при выборе.

Что я делаю неправильно?

Я мог бы хранить только строковые значения параметров и приводить их при выборе из таблицы параметров. Я пойду по этому пути, если не смогу понять, почему этот вызов не работает должным образом.

Это просто слабость типа SQL_Variant? Что-то неправильно настроено на сервере SQL?

SQL Server 2012 стандарт. Версия базы данных такая же.

0

sql-server t-sql stored-procedures variant sql-variant

Источник

user8004748 06 мар '18 в 19:26

1 ответ

Другие вопросы по тегам sql-server t-sql stored-procedures variant sql-variant

user8004748 06 мар '18 в 21:30 2018-03-06 21:30 · Answer 1 · 2018-03-06 21:30

Ну, это то, что я закончил. Функционально делает то же самое без использования sql_variants.

CREATE PROCEDURE [dbo].[usp_GetOrCreateCorrespondenceBatchID]
    -- Add the parameters for the stored procedure here
    @CorrespondenceBatchName VARCHAR(100)
    , @CorrespondenceTypeId int
    , @CorrespondenceBatchId INT OUTPUT
    , @isNewlyCreatedBatch BIT OUTPUT
    , @ParameterName01 NVARCHAR(100) = null, @ParamenterValue01 VARCHAR(MAX) = null
    , @ParameterName02 NVARCHAR(100) = null, @ParamenterValue02 VARCHAR(MAX) = null
    , @ParameterName03 NVARCHAR(100) = null, @ParamenterValue03 VARCHAR(MAX) = null
    , @ParameterName04 NVARCHAR(100) = null, @ParamenterValue04 VARCHAR(MAX) = null
    , @ParameterName05 NVARCHAR(100) = null, @ParamenterValue05 VARCHAR(MAX) = null
    , @ParameterName06 NVARCHAR(100) = null, @ParamenterValue06 VARCHAR(MAX) = null
    , @ParameterName07 NVARCHAR(100) = null, @ParamenterValue07 VARCHAR(MAX) = null
    , @ParameterName08 NVARCHAR(100) = null, @ParamenterValue08 VARCHAR(MAX) = null
    , @ParameterName09 NVARCHAR(100) = null, @ParamenterValue09 VARCHAR(MAX) = null
    , @ParameterName10 NVARCHAR(100) = null, @ParamenterValue10 VARCHAR(MAX) = null
AS
    BEGIN
        SET NOCOUNT ON;

        SET @isNewlyCreatedBatch = 0
        SET @CorrespondenceBatchId = NULL;

        SELECT  @CorrespondenceBatchId = CorrespondenceBatch.CorrespondenceBatchID
            FROM    dbo.CorrespondenceBatch
            WHERE   CorrespondenceBatch.[Name] = @CorrespondenceBatchName

        IF @CorrespondenceBatchId IS NULL
        BEGIN
            SET @isNewlyCreatedBatch = 1
            INSERT INTO dbo.CorrespondenceBatch
                    ( CreatedBy
                    , CreatedDate
                    , LastModifiedBy
                    , LastModifiedDate
                    , CorrespondenceTypeID
                    , [Name]
                    )
            VALUES  ( SUSER_SNAME()  -- CreatedBy - nvarchar(50)
                    , GETUTCDATE()  -- CreatedDate - datetime
                    , SUSER_SNAME()  -- LastModifiedBy - nvarchar(50)
                    , GETUTCDATE()  -- LastModifiedDate - datetime
                    , @CorrespondenceTypeId
                    , @CorrespondenceBatchName  -- Name - nvarchar(100)
                    )
            SET @CorrespondenceBatchId = SCOPE_IDENTITY() 


            IF @ParameterName01 IS NOT NULL INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, [Name], ValueString, ValueDateTime, ValueBigInt, ValueDecimal )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName01, TRY_CAST(@ParamenterValue01 AS VARCHAR(max)), TRY_CAST(@ParamenterValue01 AS DATETIME), TRY_CAST(@ParamenterValue01 AS BIGINT), TRY_CAST(@ParamenterValue01 AS DECIMAL))
            IF @ParameterName02 IS NOT NULL INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, [Name], ValueString, ValueDateTime, ValueBigInt, ValueDecimal )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName02, TRY_CAST(@ParamenterValue02 AS VARCHAR(max)), TRY_CAST(@ParamenterValue02 AS DATETIME), TRY_CAST(@ParamenterValue02 AS BIGINT), TRY_CAST(@ParamenterValue02 AS DECIMAL))
            IF @ParameterName03 IS NOT NULL INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, [Name], ValueString, ValueDateTime, ValueBigInt, ValueDecimal )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName03, TRY_CAST(@ParamenterValue03 AS VARCHAR(max)), TRY_CAST(@ParamenterValue03 AS DATETIME), TRY_CAST(@ParamenterValue03 AS BIGINT), TRY_CAST(@ParamenterValue03 AS DECIMAL))
            IF @ParameterName04 IS NOT NULL INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, [Name], ValueString, ValueDateTime, ValueBigInt, ValueDecimal )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName04, TRY_CAST(@ParamenterValue04 AS VARCHAR(max)), TRY_CAST(@ParamenterValue04 AS DATETIME), TRY_CAST(@ParamenterValue04 AS BIGINT), TRY_CAST(@ParamenterValue04 AS DECIMAL))
            IF @ParameterName05 IS NOT NULL INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, [Name], ValueString, ValueDateTime, ValueBigInt, ValueDecimal )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName05, TRY_CAST(@ParamenterValue05 AS VARCHAR(max)), TRY_CAST(@ParamenterValue05 AS DATETIME), TRY_CAST(@ParamenterValue05 AS BIGINT), TRY_CAST(@ParamenterValue05 AS DECIMAL))
            IF @ParameterName06 IS NOT NULL INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, [Name], ValueString, ValueDateTime, ValueBigInt, ValueDecimal )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName06, TRY_CAST(@ParamenterValue06 AS VARCHAR(max)), TRY_CAST(@ParamenterValue06 AS DATETIME), TRY_CAST(@ParamenterValue06 AS BIGINT), TRY_CAST(@ParamenterValue06 AS DECIMAL))
            IF @ParameterName07 IS NOT NULL INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, [Name], ValueString, ValueDateTime, ValueBigInt, ValueDecimal )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName07, TRY_CAST(@ParamenterValue07 AS VARCHAR(max)), TRY_CAST(@ParamenterValue07 AS DATETIME), TRY_CAST(@ParamenterValue07 AS BIGINT), TRY_CAST(@ParamenterValue07 AS DECIMAL))
            IF @ParameterName08 IS NOT NULL INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, [Name], ValueString, ValueDateTime, ValueBigInt, ValueDecimal )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName08, TRY_CAST(@ParamenterValue08 AS VARCHAR(max)), TRY_CAST(@ParamenterValue08 AS DATETIME), TRY_CAST(@ParamenterValue08 AS BIGINT), TRY_CAST(@ParamenterValue08 AS DECIMAL))
            IF @ParameterName09 IS NOT NULL INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, [Name], ValueString, ValueDateTime, ValueBigInt, ValueDecimal )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName09, TRY_CAST(@ParamenterValue09 AS VARCHAR(max)), TRY_CAST(@ParamenterValue09 AS DATETIME), TRY_CAST(@ParamenterValue09 AS BIGINT), TRY_CAST(@ParamenterValue09 AS DECIMAL))
            IF @ParameterName10 IS NOT NULL INSERT INTO dbo.CorrespondenceBatchParameter ( CreatedBy, CreatedDate, LastModifiedBy, LastModifiedDate, CorrespondenceBatchID, [Name], ValueString, ValueDateTime, ValueBigInt, ValueDecimal )VALUES  ( SUSER_SNAME(), GETUTCDATE(), SUSER_SNAME(), GETUTCDATE(), @CorrespondenceBatchId, @ParameterName10, TRY_CAST(@ParamenterValue10 AS VARCHAR(max)), TRY_CAST(@ParamenterValue10 AS DATETIME), TRY_CAST(@ParamenterValue10 AS BIGINT), TRY_CAST(@ParamenterValue10 AS DECIMAL))

        END
    END

При переходе через вызывающий SP в режиме отладки он просто пропускает вызов usp_GetOrCreateCorrespondenceBatchID. Поместив вызов в TRY/CATCH, я смог получить реальную ошибку "Не удается преобразовать DATETIME в BIGINT" при выполнении вызова. Так как в SQL интерпретировал sql_variant как BIGINT. Изменяя тип параметра с SQL_VARIANT на VARCHAR(MAX), я больше не получаю эту ошибку, и SP функционирует должным образом.

Я был сожжен SQL_VARIANT в прошлом! Я думаю, я больше не буду использовать SQL_VARIANT.