使变量的数据类型匹配列的数据类型

Question

局部变量的数据类型需要匹配现有表列的数据类型。

在过去，我会查找列的数据类型并手动匹配，如下所示：

-- schema follows...
CREATE TABLE [dbo].[TestTable]
(
    [Id] BIGINT NOT NULL PRIMARY KEY, 
    [valueholder] NVARCHAR(MAX) NULL
)

...
-- manually set data type to match above
DECLARE @tempvalueholder AS NVARCHAR(MAX)

问题是，如果模式在这条线上发生了变化，我将不得不手动查找和更新。

假设列名和表名保持不变，是否有办法将局部变量的数据类型与列的数据类型联系起来？

我知道如何以类似于此的方式获取数据类型，但不知道如何连接到变量声明：

SELECT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='testtable'
AND COLUMN_NAME='valueholder'

Answer 1

您需要动态创建“声明@VariableName数据类型”。这意味着变量和代码的其余部分应该在动态SQL的范围内。如果这符合您的需要，您可以尝试如下：

DECLARE
    @DataType1 VARCHAR(16)
    , @DataType2 VARCHAR(16)
    , @DynamicSQL NVARCHAR(MAX) = N'';

SELECT
    @DataType1 = UPPER(DATA_TYPE)
    , @DataType2 = 
        CASE
            WHEN (DATA_TYPE IN ('char', 'nchar')) THEN CONCAT(UPPER(DATA_TYPE), '(', CHARACTER_MAXIMUM_LENGTH, ')')
            WHEN (DATA_TYPE IN ('varchar', 'nvarchar')) THEN CASE WHEN (CHARACTER_MAXIMUM_LENGTH = -1) THEN CONCAT(UPPER(DATA_TYPE), '(MAX)') ELSE CONCAT(UPPER(DATA_TYPE), '(', CHARACTER_MAXIMUM_LENGTH, ')') END
            WHEN (DATA_TYPE IN ('decimal', 'numeric')) THEN CONCAT(UPPER(DATA_TYPE), '(', NUMERIC_PRECISION, ', ', NUMERIC_SCALE, ')')
            WHEN (DATA_TYPE = 'float') THEN CASE WHEN (NUMERIC_PRECISION = 53) THEN UPPER(DATA_TYPE) ELSE CONCAT(UPPER(DATA_TYPE), '(', NUMERIC_PRECISION, ')') END
            WHEN (DATA_TYPE = 'real') THEN CASE WHEN (NUMERIC_PRECISION = 24) THEN UPPER(DATA_TYPE) ELSE CONCAT(UPPER(DATA_TYPE), '(', NUMERIC_PRECISION, ')') END
            WHEN (DATA_TYPE = 'image') THEN NULL
            WHEN (DATA_TYPE = 'time') THEN CASE WHEN (DATETIME_PRECISION = 7) THEN UPPER(DATA_TYPE) ELSE CONCAT(UPPER(DATA_TYPE), '(', DATETIME_PRECISION, ')') END
            WHEN (DATA_TYPE = 'varbinary') THEN CASE WHEN (CHARACTER_MAXIMUM_LENGTH = -1) THEN CONCAT(UPPER(DATA_TYPE), '(MAX)') ELSE CONCAT(UPPER(DATA_TYPE), '(', CHARACTER_MAXIMUM_LENGTH, ')') END
            ELSE UPPER(DATA_TYPE)
        END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_SCHEMA = 'SchemaName'
    AND TABLE_NAME = 'TableName'
    AND COLUMN_NAME = 'ColumnName';

IF(@DataType2 IS NULL)
    BEGIN RAISERROR (N'Data type %s is invalid for local variables.', 16, 1, @DataType1); END
ELSE
    BEGIN
        SET @DynamicSQL += N'
DECLARE @VariableName ' + @DataType2 + ';'

        SET @DynamicSQL += N'
SET @VariableName = 15;

SELECT @VariableName AS [@VariableName];
';

        EXEC (@DynamicSQL);
    END

Answer 2

与Oracle不同的是，在sql-server中不支持这一点。

唯一可以做的事情是，当表的架构发生变化时，查找访问此表并检查/更正声明的所有过程、触发器、函数。

下面是一个小查询，它帮助您找到使用该表的所有过程、函数和触发器：

SELECT DISTINCT
       o.name AS Object_Name,
       o.type_desc,
       m.*
FROM   sys.sql_modules m
  INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition Like '%MyTableName%';

Answer 3

我可以建议使用一个临时表，它将具有与原始表完全相同的结构。

因此，即使表列的数据类型被更改，存储过程代码也不会受到影响。

但是对于开发人员来说，它也有编码过载。

这是一个样本

create procedure TestTable10_readbyId (@Id bigint)
as
begin

DECLARE @tempvalueholder AS NVARCHAR(MAX)

select * into #t from TestTable10 where 1 = 0
insert into #t (Id) values (@Id)

update #t
set valueholder = TestTable10.valueholder
from #t
inner join TestTable10 on TestTable10.Id = #t.Id 
and TestTable10.Id = @Id

select @tempvalueholder = valueholder
from TestTable10
where Id = @Id

select @tempvalueholder as valueholder
select valueholder from #t
end

你看，我维护了这两种方法

一种是使用数据变量声明，另一种是创建临时表，从逻辑上为选择列表中的每一列生成一个变量。

当然，对于第二个解决方案，我们需要一个带空值的行。

实际上，我不喜欢这种方法，因为在编写代码时，您必须考虑所有这些。因此，我插入了一个带有空值的虚拟行，但只插入了PK字段，我认为它在下面的代码块中很有用

我没有首先通过设置变量的值来返回变量，而是更新临时表列，然后在本例中返回此行列。