4

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.

5
  • 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
  • @Charles Maybe look at sys.dm_exec_describe_first_result - the output in system_type_name means you don't have to worry about parens, halving max length for Unicode, etc. etc. Also, once you know int can't take parens, that doesn't seem to be something you need to rely on INFORMATION_SCHEMA for forever. If you're bothering to construct things with dynamic SQL anyway, you can use CASE expressions like the rest of us. – Aaron Bertrand Dec 14 '16 at 13:49
9

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.

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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