問(wèn)題描述

我正在考慮創(chuàng)建一個(gè)格式的函數(shù)

I'm thinking of creating a function of the format

FULL_TYPE_NAME(type_id, max_length)

以字符串格式返回?cái)?shù)據(jù)類型和長(zhǎng)度，例如:

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

FULL_TYPE_NAME (231,-1) 

會(huì)回來(lái):

nvarchar(max)

在我這樣做之前，我想檢查一下 tsql 是否已經(jīng)有這樣的功能(我還沒(méi)有找到)，或者是否有好心人有一個(gè)現(xiàn)成的我可以使用的功能.如果沒(méi)有，那么我會(huì)寫一個(gè)并在這里發(fā)布.

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.

提前致謝.

推薦答案

粗略的開(kāi)始是這樣的:

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)

請(qǐng)注意，這僅適用于 char 數(shù)據(jù)類型 &只處理長(zhǎng)度，如果你想使用精度(小數(shù)等)，你需要實(shí)現(xiàn)更多的邏輯

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)

此外，您可能希望添加驗(yàn)證以僅允許特定用戶類型的 -1 長(zhǎng)度

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

這篇關(guān)于包含數(shù)據(jù)長(zhǎng)度的擴(kuò)展 TYPE_NAME 函數(shù)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！