NAME function that includes datalength

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.

---

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?)

---

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

---

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;

链接地址: http://www.djcxy.com/p/76322.html

上一篇: 如果存在alter table语句不为null或缺省值不起作用

下一篇: 包含数据长度的NAME函数