Search code examples
sql-servercontext-info

Weird result coverting from varchar to varbinary and converting back -- sql server 2008

Here is the stored procedure:

     CREATE PROCEDURE dbo.TestTestTest AS  
       BEGIN
         DECLARE @ProcedureIdForTracking varbinary(128) = CONVERT(varbinary(128), @@procid)
         DECLARE @ProcedureNameForTracking varbinary(128) = CONVERT(varbinary(128), OBJECT_NAME(@@procid))
         SELECT @@procid AS originalProcid, @ProcedureIdForTracking, CONVERT(bigint, @ProcedureIdForTracking) AS ConvertBackId
         SELECT OBJECT_NAME(@@procid) AS originalName, @ProcedureNameForTracking, CONVERT(varchar(1000),
     @ProcedureNameForTracking) AS ConvertBackName
         SET CONTEXT_INFO @ProcedureNameForTracking 
       END

I can recover the @@procid from the converting, but not the stored procedure name. Any idea?Anything wrong with the OBJECT_NAME function?

The result: enter image description here

Solution

  • Object names are of the datatype SYSNAME (A synonym for NVARCHAR(128)), you are therefore converting from one datatype to binary then back to another, so you are not doing the reverse. You can demonstrate this fairly simply:

    DECLARE @ProcName SYSNAME = 'dbo.TestTestTest'
DECLARE @VarBin VARBINARY(128) = CONVERT(VARBINARY(128), @Procname);


SELECT  Inccorect = CONVERT(VARCHAR(1000), @VarBin),
        Correct = CONVERT(NVARCHAR(128), @VarBin);

    Which yields:

    Inccorect   Correct
------------------------------
d           dbo.TestTestTest

    Furthermore, NVARCHAR requires 2 bytes per character, so VARBINARY(128) is not long enough to store it, again, this can be demonstrated:

    DECLARE @ProcName SYSNAME = REPLICATE('|', 128);
DECLARE @VarBin VARBINARY(128) = CONVERT(VARBINARY(128), @Procname);

SELECT  Len1 = LEN(@ProcName),
        Len2 = LEN(CONVERT(NVARCHAR(128), @VarBin));

    Which gives:

    Len1    Len2
-----------------
128     64

    So in you would need to adjust your types and lengths:

    DECLARE @ProcedureIdForTracking varbinary(128) = CONVERT(varbinary(128), @@procid)
DECLARE @ProcedureNameForTracking varbinary(256) = CONVERT(varbinary(128), OBJECT_NAME(@@procid))
SELECT @@procid AS originalProcid, @ProcedureIdForTracking, CONVERT(bigint, @ProcedureIdForTracking) AS ConvertBackId
SELECT OBJECT_NAME(@@procid) AS originalName, @ProcedureNameForTracking, CONVERT(nvarchar(128),
 @ProcedureNameForTracking) AS ConvertBackName
SET CONTEXT_INFO @ProcedureNameForTracking