CODEWITHSUNDEEP
sqlsql-server-2005t-sql
hunterjrj
hunterjrj

Reputation: 772

SQL Server 2005: Determine datatype of variable

Is it possible to determine the type of a local variable at runtime in TSQL?

For example, say I wanted to do something along these lines:

IF ( @value IS INTEGER )

Or

IF ( TYPEOF(@value) = <whatever> )

Does anyone know of any way to accomplish this?

EDIT: This is not for a specific task, this is more of a general knowledge question. I do appreciate answers that indicate that the type should be known since it is declared within the same batch, I am curious as to whether the type can be determined at runtime.

Upvotes: 16

Views: 22201

Answers (3)

HO LI Pin
HO LI Pin

Reputation: 1681

using SQL 2019 , below code still working

DECLARE @MyVar int;SET @MyVar = 99;
DECLARE @MyVarDataType varchar(50);

SET @MyVarDataType  = cast(SQL_VARIANT_PROPERTY(@MyVar,'BaseType') AS varchar);

PRINT @MyVarDataType

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135011

run this

declare @d int

select @d = 500

if cast(sql_variant_property(@d,'BaseType') as varchar(20))  = 'int'
print 'yes'
else
print 'no'

Upvotes: 32

DJ.
DJ.

Reputation: 16247

I don't think so - BUT it is a local variable so are declaring it in the same procedure so you would know the type anyways - or am I missing something?

Upvotes: -1

Related Questions