问题描述
是否可以在 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 上的列
这篇关于使用变量转换数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！