CODEWITHSUNDEEP
sql-server
p.developer
p.developer

Reputation: 57

storing different languages in sql table

Using SQL server management, creating tables for a glossary for example where you will need different translations for different words .. i am storing 15 different languages such as french, italian, spanish .. but when storing these in a table theyre are outputted fine, but when creating a table valued function they dont output correctly. my return is a simple select and for Bulgarian it just outputs question marks

Is there any way to help sql management to read these languages better?

appreciate the help

DATABASE STRUCTURE

ALTER FUNCTION [dbo].SelectExportObject ( @TITLE NVARCHAR(MAX) ) 
RETURNS @Result TABLE
( 
  BG                NVARCHAR(MAX) ,
  CS                NVARCHAR(MAX) ,
  DE                NVARCHAR(MAX) ,
  EL                NVARCHAR(MAX) ,
  EN                NVARCHAR(MAX) ,
  ES                NVARCHAR(MAX) ,
  ET                NVARCHAR(MAX) ,
  FI                NVARCHAR(MAX) ,
  FR                NVARCHAR(MAX) ,
  GA                NVARCHAR(MAX) ,
  HU                NVARCHAR(MAX) ,
  IT                NVARCHAR(MAX) ,
  LT                NVARCHAR(MAX) 
 )

AS
BEGIN

    DECLARE @BG VARCHAR(MAX) 
    DECLARE @CS VARCHAR(MAX)
    DECLARE @DE VARCHAR(MAX)
    DECLARE @EL VARCHAR(MAX)
    DECLARE @EN VARCHAR(MAX)
    DECLARE @ES VARCHAR(MAX)
    DECLARE @ET VARCHAR(MAX)
    DECLARE @FI VARCHAR(MAX)
    DECLARE @FR VARCHAR(MAX)
    DECLARE @GA VARCHAR(MAX)
    DECLARE @HU VARCHAR(MAX)
    DECLARE @IT VARCHAR(MAX)
    DECLARE @LT VARCHAR(MAX)


    select  @BG = c.BG,
           @CS = c.CS, 
           @DE = c.DE, 
           @EL = c.EL, 
           @EN = c.EN, 
           @ES = c.ES, 
           @ET = c.ET, 
           @FI = c.FI, 
           @FR = c.FR, 
           @GA = c.GA,
           @HU = c.HU,
           @IT = c.IT,  
           @LT = c.LT
    from tblTEST c where c.title = @Title


    /* Populate the values of @Str1 and @Str2 how ever you need to */

 INSERT INTO @Result (BG,CS,DE,EL,EN,ES,ET,FI,FR,GA,HU,IT,LT)
 VALUES (@BG,@CS,@DE,@EL,@EN,@ES,@ET,@FI,@FR,@GA,@HU,@IT,@LT)

    RETURN;
END

Upvotes: 0

Views: 2120

Answers (1)

Thom A
Thom A

Reputation: 95557

There's really no need for this to be a multi line table-value function, it'll perform much worse than an inline table-value function.

The problem you have is that you have declared your variables as a varchar, not an nvarchar, however, if we change it to a inline table-value function, then the problem doesn't even exist (as those variables disappear):

ALTER FUNCTION [dbo].SelectExportObject ( @Title nvarchar(MAX) ) 
RETURNS TABLE
AS

    SELECT t.BG,
           t.CS, 
           t.DE, 
           t.EL, 
           t.EN, 
           t.ES, 
           t.ET, 
           t.FI, 
           t.FR, 
           t.GA,
           t.HU,
           t.IT,  
           t.LT
    FROM tblTEST t
    WHERE t.title = @Title;

GO

Upvotes: 2

Related Questions