[SOLVED] Is there any way to get lcid of current session in a function with schemabinding option?

Is there any way to get lcid of current session in a function with schemabinding option?

I need to get lcid of current session in a function. In a general case, I can get @@LANGID and then match it with sys.syslanguages table. But not in the function or view compiled with schemabinding option. Are there any other ways to get lcid without a direct call to the syslanguages table?

Solution

I don't know that syslanguages has changed in decades, so you could just make your own user table for lookups.

SELECT lcid, [langid] INTO dbo.LangMapping FROM sys.syslanguages;
GO
CREATE UNIQUE CLUSTERED INDEX lm ON dbo.LangMapping(langid);

Now you can create a schema-bound function:

CREATE FUNCTION dbo.GetLCIDs(@langid int)
RETURNS table
WITH SCHEMABINDING
AS
  RETURN 
  (
    SELECT lcid 
      FROM dbo.LangMapping 
     WHERE langid = COAELSCE(@langid, @@LANGID)
  );

(If you just want the current LCID, pass in null.)

If you're worried about the data changing over time, you could set up a job to periodically repopulate the table.

An alternative, as there are only 34 rows, you could easily hard-code them into their own function or view using a CASE expression (which you could also periodically rebuild if you're worried about lcid/langid mapping changing over time). You could build it using:

SELECT CONCAT_WS( char(13)+char(10), 
       '  WHEN ', [langid], ' THEN ', lcid)
  FROM sys.syslanguages;

Or if you want it to look really pretty, and include the language alias for self-documentation:

SELECT CONCAT(N' WHEN ', RIGHT(CONCAT(space(2), [langid]), 2), 
  N' /* ', alias, SPACE(19-LEN(alias)), N' */ THEN ', lcid, 
  char(13), char(10))
FROM sys.syslanguages;

Then plug that into:

CREATE OR ALTER FUNCTION dbo.GetLCID(@langid int)
RETURNS table
WITH SCHEMABINDING
AS
BEGIN
  RETURN (SELECT lcid = CASE COALESCE(@langid, @@LANGID)
    ... 34 whens ...
  END);
END

e.g.

CREATE OR ALTER FUNCTION dbo.GetLCIDs(@langid int)
RETURNS table
WITH SCHEMABINDING
AS
  RETURN (SELECT lcid = CASE COALESCE(@langid, @@LANGID)
 WHEN  0 /* English             */ THEN 1033
 WHEN  1 /* German              */ THEN 1031
 WHEN  2 /* French              */ THEN 1036
 WHEN  3 /* Japanese            */ THEN 1041
 WHEN  4 /* Danish              */ THEN 1030
 WHEN  5 /* Spanish             */ THEN 3082
 WHEN  6 /* Italian             */ THEN 1040
 WHEN  7 /* Dutch               */ THEN 1043
 WHEN  8 /* Norwegian           */ THEN 2068
 WHEN  9 /* Portuguese          */ THEN 2070
 WHEN 10 /* Finnish             */ THEN 1035
 WHEN 11 /* Swedish             */ THEN 1053
 WHEN 12 /* Czech               */ THEN 1029
 WHEN 13 /* Hungarian           */ THEN 1038
 WHEN 14 /* Polish              */ THEN 1045
 WHEN 15 /* Romanian            */ THEN 1048
 WHEN 16 /* Croatian            */ THEN 1050
 WHEN 17 /* Slovak              */ THEN 1051
 WHEN 18 /* Slovenian           */ THEN 1060
 WHEN 19 /* Greek               */ THEN 1032
 WHEN 20 /* Bulgarian           */ THEN 1026
 WHEN 21 /* Russian             */ THEN 1049
 WHEN 22 /* Turkish             */ THEN 1055
 WHEN 23 /* British English     */ THEN 2057
 WHEN 24 /* Estonian            */ THEN 1061
 WHEN 25 /* Latvian             */ THEN 1062
 WHEN 26 /* Lithuanian          */ THEN 1063
 WHEN 27 /* Brazilian           */ THEN 1046
 WHEN 28 /* Traditional Chinese */ THEN 1028
 WHEN 29 /* Korean              */ THEN 1042
 WHEN 30 /* Simplified Chinese  */ THEN 2052
 WHEN 31 /* Arabic              */ THEN 1025
 WHEN 32 /* Thai                */ THEN 1054
 WHEN 33 /* Bokmål              */ THEN 1044
END);

Some sample usage here:

db<>fiddle