Maybe there is an answer for this somewhere but, I couldn't find any efficient answer on Google for this question, therefore, I will ask this here.

I wanted to get some information about my Stored Procedure parameters dynamically, so I wrote this script:

SELECT  PARAMETER_NAME,
        DATA_TYPE,
        CHARACTER_MAXIMUM_LENGTH,
        CHARACTER_OCTET_LENGTH
FROM    INFORMATION_SCHEMA.PARAMETERS
WHERE   SPECIFIC_NAME = @ProcName

The question is what the difference between CHARACTER_MAXIMUM_LENGTH and CHARACTER_OCTET_LENGTH. Could you give me example of when they would have different results?

enter image description here

Thanks in advance.

  • 2
    Honestly, if you're just dealing with SQL Server, pretend INFORMATION_SCHEMA doesn't exist. They're incomplete, documented as unreliable (see the yellow caution signs here and here), are not being updated with new features or attributes (e.g. try to find info about filtered indexes or include columns), and don't contain anything you can't get from the sys catalog views. – Aaron Bertrand Aug 16 '14 at 12:39
  • ...just don't see any good reason to use them when you have to use the catalog views sometimes anyway. – Aaron Bertrand Aug 16 '14 at 12:55
  • @AaronBertrand So, if I want to get all parameters of stored procedure, how can I do it? – Misha Zaslavsky Aug 16 '14 at 13:36
  • sys.parameters? – Aaron Bertrand Aug 16 '14 at 13:40
  • @AaronBertrand I want to build this string dinamically: DECLARE @p0 INT = , @p1 NVARCHAR(MAX) = sys.parameters is not that comfortable for this because I need the type and the length of the parameter (if it is of type string). There are fields system_type_id/user_type_id for the type, so that I need to make a join to types. I need the max_length which is more problematic because the length of INT is 4 but I don't want to write it, only want to write if it is string, so that I probably need some more code to check if it is the type of string (NVARCHAR, VARCHAR, etc...). – Misha Zaslavsky Aug 16 '14 at 13:52
up vote 6 down vote accepted

As described in the MSDN documentation CHARACTER_OCTET_LENGTH is the length in bytes, and CHARACTER_MAXIMUM_LENGTH is the length in characters.

For parameters of type char or varchar they will be the same, but for parameters of type nchar or nvarchar they will be different, with OCTET-LENGTH being twice (usually if not always) the CHARACTER_LENGTH.

  • When it is not twice the CHARACTER_LENGTH for Unicode strings? – Paul Jun 10 '16 at 19:41
  • Check out these two links: kunststube.net/encoding and joelonsoftware.com/articles/Unicode.html – Pieter Geerkens Jun 10 '16 at 19:50
  • I know about encodings. I also assume that if the data is NVARCHAR it means two-bytes Unicode LE (Unicode is not UTF). So my question is "How to put such a data into MSSQL server table with no additional headache?". Is there any UTF field type? – Paul Jun 10 '16 at 19:57

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.