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;
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;
