Cast datatype with variable

Question

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

You can cast most types to an `sql_variant` although its usually a bad idea — Alex K., Jun 18 '12 at 15:59
@AlexK. except for geography, geometry, hierarchyid, image, text, ntext, timestamp, xml and LOB types such as varchar(max), nvarchar(max), and varbinary(max). — Aaron Bertrand, Jun 18 '12 at 16:02

score 7 · Answer 1 · edited Jul 29 '20 at 18:43

7

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?

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.

And finally, I strongly recommend sys.columns over INFORMATION_SCHEMA.

edited Jul 29 '20 at 18:43

Community

1
1

answered Jun 18 '12 at 15:50

Aaron Bertrand

272,866
37
466
490

What no mention of how the sys views are better than the information_schema views ... – Conrad Frix Jun 18 '12 at 15:51
Thanks, I was trying to find an alternative to dynamic sql. But doubted there was. I am limited by the way the DB has been setup for years (resistant to change). A couple of hundred databases with the same tables but different data types on some columns (smallint vs int), etc. Using PowerShell to load sproc's up I don't want a custom one per database. I can always go back to a .NET util I have to coerce data types but was looking at stream lining this where .NET was not the calling app. – Jun 18 '12 at 16:23

score 2 · Answer 2 · answered Jul 29 '15 at 17:11

I needed to do something similar so I created a function so that I can return the literal data type as would be needed for my dynamic SQL. I was surprised that I could not find a function like this already created: (Or maybe I shouldn't be surprised as CASTING through Dynamic SQL wouldn't by my recommendation... but at this point I can not think of an alternative as the source table is variable)

Anyway here is the function should anyone ever need it. Please note that so far I have only included data types from the table that I am trying to CAST into... so I imagine anyone who uses this will need to follow suit and include the data types they need or possibly all the rest of them.

CREATE FUNCTION dbo.GetLiteralDataType( @TableName as VARCHAR(100), @ColumnName as VARCHAR(100) )
    RETURNS VARCHAR(100)
    AS
    BEGIN

        DECLARE @DataType as VARCHAR(100)
        DECLARE @FullDataType as VARCHAR(100)
        DECLARE @MaxLength AS INT
        DECLARE @Precision AS INT
        DECLARE @Scale AS INT


        SET @DataType =(SELECT TOP 1 y.name FROM SYS.TABLES t
        INNER JOIN SYS.COLUMNS c ON t.object_id = c.object_id
        INNER JOIN SYS.TYPES y ON y.system_type_id = c.system_type_id
        WHERE t.name = @TableName AND c.name = @ColumnName)

        SET @MaxLength =(SELECT TOP 1 c.max_length FROM SYS.TABLES t
        INNER JOIN SYS.COLUMNS c ON t.object_id = c.object_id
        INNER JOIN SYS.TYPES y ON y.system_type_id = c.system_type_id
        WHERE t.name = @TableName AND c.name = @ColumnName)

        SET @Precision =(SELECT TOP 1 c.precision FROM SYS.TABLES t
        INNER JOIN SYS.COLUMNS c ON t.object_id = c.object_id
        INNER JOIN SYS.TYPES y ON y.system_type_id = c.system_type_id
        WHERE t.name = @TableName AND c.name = @ColumnName)

        SET @Scale =(SELECT TOP 1 c.scale FROM SYS.TABLES t
        INNER JOIN SYS.COLUMNS c ON t.object_id = c.object_id
        INNER JOIN SYS.TYPES y ON y.system_type_id = c.system_type_id
        WHERE t.name = @TableName AND c.name = @ColumnName)

        IF @DataType ='decimal'
        BEGIN
            SET @FullDataType = 'DECIMAL('+ @Precision + ',' + @Scale + ')'
        END

        IF @DataType ='varchar'
        BEGIN
            SET @FullDataType = 'VARCHAR('+ @MaxLength + ')'
        END

        IF @DataType ='bit'
        BEGIN
            SET @FullDataType = 'BIT'
        END

        IF @DataType ='money'
        BEGIN
            SET @FullDataType = 'MONEY'
        END

        IF @DataType ='date'
        BEGIN
            SET @FullDataType = 'DATE'
        END

        RETURN @FullDataType 
    END

Cast datatype with variable

2 Answers2

Linked