使用变量转换数据类型 [英] Cast datatype with variable

问题描述

是否可以在 T-SQL 中将数据类型转换为变量类型?

Is it possible in T-SQL to cast a data type with a variable type?

即虽然这行不通，因为它需要一个字符串文字，但可以理解要点:

I.e. Though this won't work since it expects a string literal but gets the gist across:

select @DataType = Data_Type
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'emp'
and COLUMN_NAME = 'emp_id'

SELECT
cast(emp_id as @DataType)
FROM emp

推荐答案

不，您需要为此使用动态 SQL，例如

No, you will need to use dynamic SQL for this, e.g.

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = N'SELECT CAST(emp_id AS ' + Data_Type
 + ') FROM dbo.emp;'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'emp'
AND TABLE_SCHEMA = 'dbo' -- this might be important!
AND COLUMN_NAME = 'emp_id';

PRINT @sql;
-- EXEC sp_executesql @sql;

然而，这对我来说似乎是错误的.为什么不知道列的数据类型?

However, this seems wrong to me. Why don't you know the data types of your columns?

此外，您将需要一个更复杂的查询，因为您需要考虑精度/比例/最大长度.例如，如果 emp_id 是 varchar，则所有结果都将被截断为一个字符.

And in addition, you will need a much more complex query as you will want to take precision / scale / max length into account. If emp_id is a varchar, for example, all of the results will be truncated to one character.

最后，我强烈推荐 sys.INFORMATION_SCHEMA 上的列.

这篇关于使用变量转换数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！