5870

Extended TYPE_NAME function that includes datalength

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.

Answer1:

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

Answer2:

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

Answer3:

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;

Answer4:

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;

Recommend

  • jsonp and post action in ionic framework (angular.js)
  • change the priority of selected attribute in listbox
  • How to populate database values in dropdwon in laravel
  • Excel, Array Formulas, N/A outside of range, and ROW()
  • Retrieving table schema information using C#
  • NetworkCredential.Domain Property
  • Is there any way to distinguish between an unset property and a property set to undefined? [duplicat
  • Correspondece between Git client and server versions
  • In x86 assembly, is it better to use two separate registers for imul?
  • Removing the Edit on Github link when using Read The Docs & Sphinx with ReadTheDocs Theme
  • Summarize a data.table across multiple columns
  • Formula for Unique Hash from Integer Pair
  • Adding a CSS class to element on ng-click
  • Extract I18n translation keys from rails project
  • ElasticSearch: Full-Text Search made easy
  • Webpage with wide iframe is not scrollable on an iPhone with viewport
  • Pointers in c++ pass by value/reference
  • Creating PyPi package - Could not find a version that satisfies the requirement iso8601
  • Highest supported JSF version for JBoss 4.0?
  • ArrayList in C#
  • Python importing a function with file's name contained in a variable
  • MySQL Query Tuning - Why is using a value from a variable so much slower than using a literal?
  • file read() const correctness
  • Fast way to alphabetically sort the contents of a file in java
  • Are there algorithms for putting a digest into the file being digested?
  • Why is it ambiguous to call overloaded ambig(long) and ambig(unsigned long) with an integer literal?
  • Update SQL MS Access 2010
  • True privateness in Python
  • LibGdx GLES2.0 cube texel stretching
  • Scrolling News Ticker Jquery - Issues
  • Jhipster: How to create relationships with User entity using supplied tools?
  • Is there an easy way to associate an event with a ListViewItem?
  • Is there a way to link a linux's thread TID and a pthread_t “thread ID”
  • Motorola barcode scanner SDK events C#
  • Angular2 - Template reference inside NgSwitch
  • How can I run DataNucleus Bytecode Enhancer from SBT?
  • For loop with if condition on multiple R functions
  • Jquery Knockout: ko.computed() vs classic function?
  • How to define and use opencv mat of user type
  • Azure Cloud Service Web Role web pages do not load