find data type of a field in a select

Question

find data type of a field in a select

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

noeld (9/24/2007)
Honestly I don't understand how much faster do you get when BOL has the "return type" and is very easy to bookmark/use 😉

Yup... I agree... said something similar above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

Ninja's_RGR'us (9/24/2007)
As I said, while IT DOESN'T help for this case... that function has a lot of good info...
:).

Ah... got it... thought you'd stumbled into something cool. Thanks, Remi.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Nah, I wish.... maybe in Katmai. Anyone has access to those books online?? I have not installed the ctp yet!

mengus Ten Centuries Points: 1187 More actions · Answer 4

In response to the original post,

From my past experiences, this is the closest example I can think of. The example uses SQL_Variant_Property() System MetaData Function, which should be present in both SQL Server 2000 and SQL Server 2005.

Link to documentation from Microsoft:

http://msdn2.microsoft.com/en-us/library/ms178550.aspx

Example:

declare

@date datetime

declare

@int int

set

@date=getdate()

set

@int=15

SELECT

@date as 'Data',SQL_VARIANT_PROPERTY(@date,'BaseType') AS 'Base Type'

SELECT

@int as 'Data',SQL_VARIANT_PROPERTY(@int,'BaseType') AS 'Base Type'

SELECT

@@ServerName as 'Data',SQL_VARIANT_PROPERTY(@@ServerName,'BaseType') AS 'Base Type'

Regards,

Wameng Vang

MCTS

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Quick and dirty. Nice solution. I still don't know why I'd use this but I'll keep it in mind just in case :).

mengus Ten Centuries Points: 1187 More actions · Answer 6

mengus

Ten Centuries

Points: 1187

September 24, 2007 at 10:34 pm

#735880

Thanks

MCTS

Sergiy SSC Guru Points: 110208 More actions · Answer 7

It works perfectly for all datatypes allowing implicit conversion to SQL_VARIANT.

This is true for almost all datatypes excluding timestamp, image, text and ntext.

It will fail for those types.

And for "char" and "decimal/numeric" datatypes you'll probably need to query 'Precision' and 'Scale' properties as well.

_____________
Code for TallyGenerator

mengus Ten Centuries Points: 1187 More actions · Answer 8

Looks like someone read the documentation.... awesome... LOLz....

MCTS

Sergiy SSC Guru Points: 110208 More actions · Answer 9

It's something my fingers can do even I'm asleep:

F1 -> C+A+S+T -> Enter -> Enter

🙂

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

Heh... lemme change my stance on this subject ...

I pretty sure there's something built in ... 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Ninja's_RGR'us

SSC Guru

Points: 294069

September 25, 2007 at 4:59 am

#735948

Damn Jeff, I wish as was as smart as you :P.