question on datatyping

[misterhux]

quick question is there any way in SQL server 2000 to get the type of an field ... ie something like

select ID, "TypeOf"(ID), Name, "TypeOf"(Name) from blah_table

returning something like
ID Name
---- ------- -------- --------
1 integer Andy nvarchar
3 integer Susan nvarchar
41 integer Sam nvarchar
etc....

???????
Thanks in advance

 

[Walid]

Can ID be anything else other than int. For example can I id you by saying you are the 19.2 person that I invited to my house????????
Anyways, if you want to make sure that the value is just numbers and has no Alpha, use ISNUMERIC()

Walid Magd
Engwam@Hotmail.com

 

[misterhux]

FYI ... figured it out by creating own function

CREATE function dbo.udf_columnDataType
	(@txtTableName nvarchar(50), @txtColumnName nvarchar(50))
returns nvarchar(25)
AS
BEGIN
-- purpose is to return the data type of the supplied column
declare @txtReturn nvarchar(25)
declare @txtDataType nvarchar(20)
declare @txtDataLength nvarchar(10)

select @txtDataType = convert(nvarchar(18), data_type) ,
       @txtDataLength = 
        case data_type
          when 'nvarchar' then
            '(' + convert(nvarchar(10), CHARACTER_MAXIMUM_LENGTH) + ')'
          when 'decimal' then
            '(' + convert(nvarchar(15), NUMERIC_PRECISION) + ', ' + convert(nvarchar(15), NUMERIC_SCALE) + ')'
          else  ''
        end
       from INFORMATION_SCHEMA.COLUMNS
       where table_catalog = db_name() and 
             table_name = @txtTableName and
             column_name = @txtColumnName

set @txtReturn = @txtDataType + ' ' + @txtDataLength

return @txtReturn
END

this returns the datatype and size/precision of the column

 

[Walid]

FYI.. Glad you did it

Walid Magd
Engwam@Hotmail.com