Extended TYPE_NAME function that includes dataleng

Question

I'm thinking of creating a function of the format

FULL_TYPE_NAME(type_id, max_length)

that returns both the datatype and length in string format eg.:

FULL_TYPE_NAME (231,-1) 

would return:

nvarchar(max)

Before I do this I wanted to check if tsql already has such a function (I haven't found one) or whether some kind soul out there has a ready made one that I can use. If not, then I'll write one and post it here.

Thanks in advance.

Answer 1

Here is my final function. I think it covers everything but feel free to correct me. I'll post my test script for this function later

CREATE FUNCTION dbo.full_type_name (
 @User_Type_Id int,
 @Length smallint = NULL,
 @Precision tinyint = NULL,
 @Scale tinyint = NULL) 
RETURNS varchar (50) 
AS
BEGIN
  DECLARE
  @Returnstr varchar (50) = NULL,
  @True bit = 1,
  @False bit = 0,
  @Params_Ok bit = 1;

SELECT @Params_Ok = CASE
  --non unicode text
  WHEN ((@User_Type_Id = 165 OR @User_Type_Id = 167 
      OR @User_Type_Id = 173 OR @User_Type_Id = 175) 
     AND ((@Length < -1) OR (@Length = 0) OR (@Length > 8000))) THEN @False
     --unicode text
     WHEN ((@User_Type_Id = 231 OR @User_Type_Id = 239) 
      AND ((@Length < -1) OR (@Length = 0) OR (@Length > 4000))) THEN @False
     --decimal and numeric                          
     WHEN ((@User_Type_Id = 106 OR @User_Type_Id = 108) 
      AND (((@Precision IS NULL) AND (@Scale IS NOT NULL)) 
      OR ((@Precision IS NOT NULL) AND (@Scale IS NULL)) 
      OR (@Precision <=0) OR (@Scale <0) 
      OR (@Precision <= @Scale))) THEN @False
      --float
      WHEN ((@User_Type_Id = 62) AND ((@Precision <= 0) 
        OR (@Precision > 53))) THEN @False
      --time, datetime2 and datetimeoffset
      WHEN ((@User_Type_Id BETWEEN 41 AND 43) 
        AND ((@Precision < 0) OR (@Precision > 7))) 
          THEN @False
      END;
IF(@Params_Ok = @False) 
    BEGIN
        RETURN NULL;
    END;

SELECT @Returnstr = CASE
                      WHEN(@User_Type_Id = 129)THEN 'geometry'
                      WHEN(@User_Type_Id = 130)THEN 'geography'
                      ELSE TYPE_NAME (@User_Type_Id) 
                    END;

--nvarchar and nchar characters occupy two bytes
IF ((@Returnstr = 'nvarchar' OR @Returnstr = 'nchar') AND @Length > 0) 
    BEGIN
        SET @Length = (@Length / 2);
    END;

SELECT @Returnstr = @Returnstr + CASE
  WHEN ((@Returnstr LIKE '%varchar' OR @Returnstr = 'varbinary') 
    AND @Length < 0) 
    THEN('(' + 'max' + ')') 
  WHEN (((@Returnstr LIKE '%char') OR (@Returnstr LIKE '%binary')) 
    AND @Length >= 0) 
    THEN ('(' + CAST(@Length AS varchar) + ')') 
  WHEN ((@Returnstr = 'decimal' OR @Returnstr = 'numeric') 
    AND @Precision IS NOT NULL) 
    THEN ('(' + CAST(@Precision AS varchar) + ',' 
      + CAST(@Scale AS varchar) + ')') 
  WHEN (@Returnstr = 'float' AND @Precision IS NOT NULL) 
    THEN ('(' + CAST(@Precision AS varchar) + ')') 
  --time, datetime2 and datetimeoffset
  WHEN ((@User_Type_Id BETWEEN 41 AND 43) 
    AND (@Precision IS NOT NULL)) 
    THEN ('(' + CAST(@Precision AS varchar) + ')') 
  ELSE ''
 END;
 RETURN @Returnstr;
END;

Answer 2

This is my function. Thanks to HeavenCore for the start point

CREATE FUNCTION dbo.full_type_name (
 @User_Type_Id int,
 @Length int) 
RETURNS varchar (50) 
AS
BEGIN
    DECLARE @Returnstr varchar (50) ;

    --Handle invalid values for @Length
    IF (@Length = 0 OR @Length < -1 OR @Length > 8016 OR @Length IS NULL) 
        BEGIN
            SET @Returnstr = NULL;
        END;
    ELSE
        BEGIN
            SELECT @Returnstr = TYPE_NAME (@User_Type_Id) ;
            --unicode characters occupy two bytes
            IF ((@Returnstr = 'nvarchar' OR @Returnstr = 'nchar') AND @Length > 0) 
                BEGIN
                    SET @Length = (@Length / 2);
                END;

           SELECT @Returnstr = @Returnstr + CASE
           WHEN @Returnstr LIKE '%varchar' AND @Length < 0 
              THEN('(' + 'max' + ')') 
           WHEN @Returnstr LIKE '%char' AND @Length >= 0 
              THEN ('(' + CAST(@Length AS varchar + ')') 
           ELSE ''
         END;
        END;
    RETURN @Returnstr;
END;

I called

Select name, 
  user_type_id, 
  max_length, 
  dbo.full_type_name(ty.user_type_id, ty.max_length) as [full_type_name] 
from sys.types as ty

to test it ( visual check only) Any suggestions for improvements much appreciated

Answer 3

A rough start would be something like this:

CREATE FUNCTION udf_GetDataTypeAsString
    (
      @user_type_id INT ,
      @Length INT
    )
RETURNS VARCHAR(50)
AS 
    BEGIN
        DECLARE @ReturnStr VARCHAR(50)

        IF @Length = -1 
            SELECT  @ReturnStr = UPPER(name) + '(MAX)'
            FROM    sys.types
            WHERE   user_type_id = @user_type_id
        ELSE 
            SELECT  @ReturnStr = UPPER(name) + '(' + CONVERT(VARCHAR, @Length) + ')'
            FROM    sys.types
            WHERE   user_type_id = @user_type_id

        RETURN @ReturnStr

    END
GO

SELECT dbo.udf_GetDataTypeAsString(167, -1)
--#### Returns VARCHAR(MAX)
SELECT dbo.udf_GetDataTypeAsString(231, 24)
--#### Returns NVARCHAR(24)

Note that this is only really good for char data types & only handles length, You'd need to implement a bit more logic if you want to use precision (decimals etc)

Also, you may want to add validation to only allow -1 length on certain user types

(For the sake of curiosity – why do you want to do this?)

Answer 4

This is my script for testing my function for anyone who would be interested.

--unit test script for dbo.full_type_name
DECLARE
 @Datatypes varchar (max) = '',
 @User_Type_Ids varchar (max) = '',
 @Lengths varchar (max) = '',
 @Precisions varchar (max) = '',
 @Scales varchar (max) = '',
 @Ret_Datatypes varchar (max) = '',
 @Utid_Index int = 0,
 @Length_Index int = 0,
 @Precision_Index int = 0,
 @Scale_Index int = 0,
 @Utid_Substr varchar (40) = '',
 @Length_Substr varchar (40) = '',
 @Precision_Substr varchar (40) = '',
 @Scale_Substr varchar (40) = '',
 @Length smallint = NULL,
 @Precision tinyint = NULL,
 @Scale tinyint = NULL,
 @Count smallint = 0,
 @Ispass bit = 0;

SELECT @Datatypes = COALESCE (@Datatypes + ',', '') + sys.types.name
  FROM sys.types
  WHERE(system_type_id < 242) 
  ORDER BY name;

--remove delimter at start of the string
SELECT @Datatypes = SUBSTRING (@Datatypes, 2, LEN (@Datatypes)) ;

SET @Datatypes = @Datatypes + ',nvarchar(10),nvarchar(max),nchar(20),char(20),
  varchar(max),varchar(10),decimal(10,5),numeric(8,4),float(4),datetime2(2),
  time(4),datetimeoffset(3),varbinary(max)';

SELECT @User_Type_Ids = COALESCE (@User_Type_Ids + ',', '') 
   + CAST(sys.types.user_type_id AS varchar (3)) 
 FROM sys.types
 WHERE(system_type_id < 242) 
 ORDER BY name;
--remove delimter at start of the string
SELECT @User_Type_Ids = SUBSTRING (@User_Type_Ids, 2, LEN (@User_Type_Ids)) ;

SELECT @User_Type_Ids = @User_Type_Ids + ',231,231,239,175,167,167,106,108,62,42,41,43,165';

SELECT @Lengths = '8,null,1,null,3,8,8,10,17,8,null,null,892,16,4,8,null,
  16,17,null,4,4,2,4,8016,256,16,5,8,1,16,null,null,-1';

--now add the remainimg lengths
  SELECT @Lengths = @Lengths + ',20,-1,40,20,-1,10,17,17,8,8,5,10,-1';

WHILE(@Count < 34)  --don't use values from sys.types
  BEGIN
    SELECT @Precisions = @Precisions + ',' + 'null';
    SELECT @Scales = @Scales + ',' + 'null';
    SET @Count = @Count + 1;
  END;

--remove delimter at start of the string
SELECT @Precisions = SUBSTRING (@Precisions, 2, LEN (@Precisions)) ;
--now add the remaining precisions
SELECT @Precisions = @Precisions + ',null,null,null,null,null,null,10,8,4,2,4,3,null';

--remove delimter at start of the string
SELECT @Scales = SUBSTRING (@Scales, 2, LEN (@Scales)) ;
--now add remaining scales
SELECT @Scales = @Scales + ',null,null,null,null,null,null,5,4,null,null,null,null,null';

--Now call our new function for each set of parameters in turn and concatenate the result

--use a while loop to step though comma seperated pseudo arrays
WHILE @User_Type_Ids <> ''
  BEGIN
    --Find index of next commas
    SET @Utid_Index = CHARINDEX (',', @User_Type_Ids) ;
    SET @Length_Index = CHARINDEX (',', @Lengths) ;
    SET @Precision_Index = CHARINDEX (',', @Precisions) ;
    SET @Scale_Index = CHARINDEX (',', @Scales) ;
    IF @Utid_Index > 0
        BEGIN
            -- @utid_substr is substring from start of @CsRoles to character preceding the comma
            SET @Utid_Substr = LEFT (@User_Type_Ids, @Utid_Index - 1) ;
            SET @Length_Substr = LEFT (@Lengths, @Length_Index - 1) ;
            SET @Precision_Substr = LEFT (@Precisions, @Precision_Index - 1) ;
            SET @Scale_Substr = LEFT (@Scales, @Scale_Index - 1) ;
            --now remove this substring and its trailing comma from @CsRoles
            SET @User_Type_Ids = RIGHT (@User_Type_Ids, LEN (@User_Type_Ids) - @Utid_Index) ;
            SET @Lengths = RIGHT (@Lengths, LEN (@Lengths) - @Length_Index) ;
            SET @Precisions = RIGHT (@Precisions, LEN (@Precisions) - @Precision_Index) ;
            SET @Scales = RIGHT (@Scales, LEN (@Scales) - @Scale_Index) ;
        END;
    ELSE
        --last element, has no trailing comma
        BEGIN
            SET @Utid_Substr = @User_Type_Ids;
            SET @Length_Substr = @Lengths;
            SET @Precision_Substr = @Precisions;
            SET @Scale_Substr = @Scales;
            --ensure that we will exit the WHILE loop
            SET @User_Type_Ids = ''; --to ensure break out of while loop
        END;

    IF(@Length_Substr = 'null') 
        BEGIN
            SET @Length = NULL;
        END;
    ELSE
        BEGIN
            SET @Length = CAST(@Length_Substr AS smallint);
        END;
    IF(@Precision_Substr = 'null') 
        BEGIN
            SET @Precision = NULL;
        END;
    ELSE
        BEGIN
            SET @Precision = CAST(@Precision_Substr AS tinyint);
        END;

    IF(@Scale_Substr = 'null') 
        BEGIN
            SET @Scale = NULL;
        END;
    ELSE
        BEGIN
            SET @Scale = CAST(@Scale_Substr AS tinyint);
        END;

    --now call our function
    SET @Ret_Datatypes = @Ret_Datatypes + ',' 
       + ISNULL (jc_utils.jpwfn_0001_full_type_name (CAST(@Utid_Substr AS int), 
       @Length, @Precision, @Scale) , 'NULL') ;

END; --End of WHILE loop

--Now compare the concatenated return values with the expected vlaues
--remove delimter at start of the string
SELECT @Ret_Datatypes = SUBSTRING (@Ret_Datatypes, 2, LEN (@Ret_Datatypes)) ;

IF(@Ret_Datatypes = @Datatypes) 
  BEGIN
    SET @Ispass = 1;
  END;

--Now test for known fail conditions
--all these should return null
--invalid lengths 
IF(@Ispass = 1) 
  BEGIN
    SET @User_Type_Ids = '165,165,165,167,167,167,173,173,173,
      175,175,175,231,231,231,239,239,239,106,106,106,106,
      108,108,108,108,62,62,41,42,43';
    SET @Lengths = '0,8001,-2,0,8001,-2,0,8001,-2,0,8001,-2,0,
      4001,-2,0,4001,-2,-2,80,null,0,-2,80,null,0,0,null,0,0,0';
    SET @Precisions = 'null,null,null,null,null,null,null,null,
      null,null,null,null,null,null,null,null,
      null,null,null,2,0,1,null,2,0,1,0,54,8,8,8';
    SET @Scales = 'null,null,null,null,null,null,null,null,
      null,null,null,null,null,null,null,null,
      null,null,1,null,2,2,1,null,2,2,null,null,0,0,0';

    WHILE @User_Type_Ids <> ''
        BEGIN
            --Find index of next commas
            SET @Utid_Index = CHARINDEX (',', @User_Type_Ids) ;
            SET @Length_Index = CHARINDEX (',', @Lengths) ;
            SET @Precision_Index = CHARINDEX (',', @Precisions) ;
            SET @Scale_Index = CHARINDEX (',', @Scales) ;
            IF @Utid_Index > 0
                BEGIN
                    SET @Utid_Substr = LEFT (@User_Type_Ids, @Utid_Index - 1) ;
                    SET @Length_Substr = LEFT (@Lengths, @Length_Index - 1) ;
                    SET @Precision_Substr = LEFT (@Precisions, @Precision_Index - 1) ;
                    SET @Scale_Substr = LEFT (@Scales, @Scale_Index - 1) ;
                    --now remove this substring and its trailing comma 
                    SET @User_Type_Ids = RIGHT (@User_Type_Ids, LEN (@User_Type_Ids) 
                        - @Utid_Index) ;
                    SET @Lengths = RIGHT (@Lengths, LEN (@Lengths) - @Length_Index) ;
                    SET @Precisions = RIGHT (@Precisions, 
                       LEN (@Precisions) - @Precision_Index) ;
                    SET @Scales = RIGHT (@Scales, LEN (@Scales) - @Scale_Index) ;
                END;
            ELSE
                --last element, has no trailing comma
                BEGIN
                    SET @Utid_Substr = @User_Type_Ids;
                    SET @Length_Substr = @Lengths;
                    SET @Precision_Substr = @Precisions;
                    SET @Scale_Substr = @Scales;
                    --ensure that we will exit the WHILE loop
                    SET @User_Type_Ids = ''; --to ensure break out of while loop
                END;

            IF(@Length_Substr = 'null') 
                BEGIN
                    SET @Length = NULL;
                END;
            ELSE
                BEGIN
                    SET @Length = CAST(@Length_Substr AS smallint);
                END;
            IF(@Precision_Substr = 'null') 
                BEGIN
                    SET @Precision = NULL;
                END;
            ELSE
                BEGIN
                    SET @Precision = CAST(@Precision_Substr AS tinyint);
                END;

            IF(@Scale_Substr = 'null') 
                BEGIN
                    SET @Scale = NULL;
                END;
            ELSE
                BEGIN
                    SET @Scale = CAST(@Scale_Substr AS tinyint);
                END;

            --now call our function
            SET @Ret_Datatypes = dbo.full_type_name (CAST(@Utid_Substr AS int), 
               @Length, @Precision, @Scale) ;
            IF(@Ret_Datatypes IS NOT NULL) 
                BEGIN
                    SET @Ispass = 0;
                    BREAK;
                END;
            ELSE
                BEGIN
                    SET @Ispass = 1;
                END;

        END; --End of WHILE loop
  END;
IF(@Ispass = 1) 
  BEGIN
    PRINT 'pass';
  END;
ELSE
  BEGIN
    PRINT 'fail';
    PRINT @Datatypes;
    PRINT @Ret_Datatypes;
  END;