将变量作为sql_variant类型传递到存储过程中时出现问题

Question

尝试将不同类型的变量传递到接收变量的存储过程中时遇到问题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 标准。 数据库版本相同。

Answer 1

好吧，这就是我最终得到的。 在功能上做同样的事情而不使用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 中，我在进行调用时能够收到"无法将日期时间转换为 BIGINT"的实际错误。 就像在SQL中一样，将sql_variant解释为BIGINT。 通过将参数类型从 SQL_VARIANT 更改为 VARCHAR(MAX)，我不再收到此错误，并且 SP 正常工作。

我过去被SQL_VARIANT烧死了！ 我想我不会再使用SQL_VARIANT的了。