Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2010
    Posts
    75

    Unanswered: T-SQL: get column name from string?

    Ive been thinking about the best way to support multiple languages in my application.

    It needs to support 2 languages, no more, no less. I think that makes things a lot simpler than if it had to support an arbitrary number of languages, to be added later.

    Im thinking the simplest thing is to have 2 columns instead of 1, whenever text is involved. So instead of a column named Description, I would instead have 2 columns: Description_EN and Description_DE.

    I wouldnt mind getting some feedback on that idea but my real question is the following: How can I turn a string into an actual column name in T-SQL? It would really simplify things if I could do that. In my stored procedues, I could pass in a language string as a parameter, that is either "_EN" or "_DE". Then I dont need to do any conditional statements based on language in my SQL. I could just do something like this:

    Code:
    declare @lang char(3)
    Select @lang = "_DE"
    Select ID, Description + @lang From SomeTable
    Obviously that syntax doesnt work.
    Is there any syntax that does what I want here?

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Yes. I've seen it refered-to here as dynamic SQL.

    You simply construct an SQL statement as a varchar value and then use the EXECUTE statement to run that string.

    I assume it's not the most efficient way of doing things, and it probably does not conform to "best practices," but sometimes you have to do what you have to do.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Check out sp_executesql() in the manual.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Hope no one gets sick looking at this code sample, but here is a real-world example of executing dynamic SQL. This was mostly written a long time ago, before we knew that you could string multiple single-quotes together--which is why you see all those char(39)s.

    We had to write this so that we could use this one stored procedure to run against multiple copies of the same database on the same machine.

    Also, note the commented-out print statement at the end. While you are developing, it is handy to comment-out the EXEC statement and uncomment the PRINT statement.

    Code:
    ALTER       procedure [dbo].[usp_aplidr]
    
    @Databasename sysname,
    @UniqueID varchar(30),
    @Company varchar(2),
    @FromPeriod varchar(6),
    @ToPeriod varchar(6)
    
    as
    
    /*TEST SECTION
    usp_aplidr 'TestDatabase','RPS/SLATE','40','200001','200012'
    declare @DatabaseName sysname
    declare @UniqueID varchar(30)
    declare @Company varchar(2)
    declare @FromPeriod varchar(6)
    declare @ToPeriod varchar(6)
    
    set @DatabaseName='TestDatabase'
    set @UniqueID='RPS/SLATE'
    set @Company='40'
    set @FromPeriod='011900'
    set @ToPeriod='123000'
    --*/
    
    declare @SQL varchar(8000)
    
    set @SQL='
    select	tSJ1.job_no,
    	tSJ1.job_name,
    	case isnull(tSMTCH.[name],' + char(39)  + char(39) + ')
    		when ' + char(39) + char(39) + ' then ' + char(39) + ' No Tax Status Code Assigned' + char(39) + '
    		else tAPOID.local_cd+' + char(39) + ' - ' + char(39) + '+tSMTCH.[name]
    	end LocalCodeTaxStatus,
    	tAPOID.inv_no,
    	tAPOID.line_no,
    	convert(varchar,tAPOID.inv_dt,101) inv_dt,
    	tAPOID.vend_id,
    	tAPV.[name],
    	tAPOID.phase_no,
    	tAPOID.cost_no,
    	tAPOID.cost_type,
    	tAPOID.line_amt,
    	right(' + char(39) + '0' + char(39) + '+cast(tAPOID.post_prd as varchar),2)+' + char(39) + '/' + char(39) + '+cast(tAPOID.post_yr as varchar) PostingPeriodYear,
    	iAPOIP2.check_no,
    	convert(varchar,iAPOIP2.check_dt,101) check_dt
    from	dbo.tblSelectJobUNIQUEID tSJ1 with (NOLOCK)
    inner
    join	' + @DatabaseName + '.dbo.APOpenItemDtl tAPOID with (NOLOCK) on
    		tAPOID.cmpny_cd=tSJ1.cmpny_cd
    		and tAPOID.job_no=tSJ1.job_no
    		and cast(tAPOID.post_yr as varchar)+right(' + char(39) + '0' + char(39) + '+cast(tAPOID.post_prd as varchar),2) between ' + char(39) + @FromPeriod + char(39) + ' and ' + char(39) + @ToPeriod + char(39) + '
    inner
    join	' + @DatabaseName + '.dbo.APVendors tAPV with (NOLOCK) on
    		tAPV.cmpny_cd=tAPOID.cmpny_cd
    		and tAPV.vend_id=tAPOID.vend_id	
    left
    join	' + @DatabaseName + '.dbo.SMTaxCdHdr tSMTCH with (NOLOCK) on
    		tSMTCH.cmpny_cd=tAPOID.cmpny_cd
    		and tSMTCH.tax_cd=tAPOID.local_cd
    left
    join	(
    	select	iAPOIP1.cmpny_cd,
    		iAPOIP1.vend_id,
    		iAPOIP1.inv_no,
    		iAPOIP1.line_no,
    		iAPOIP1.check_no,
    		iAPOIP1.check_dt
    	from	' + @DatabaseName + '.dbo.APOpenItemPay iAPOIP1 with (NOLOCK)
    	inner
    	join	(
    		select	cmpny_cd,
    			vend_id,
    			inv_no,
    			line_no,
    			max(disb_no) MaxDisbNo
    		from	' + @DatabaseName + '.dbo.APOpenItemPay with (NOLOCK)
    		group
    		by	cmpny_cd,
    			vend_id,
    			inv_no,
    			line_no
    		) iAPOIP on
    			iAPOIP.cmpny_cd=iAPOIP1.cmpny_cd
    			and iAPOIP.vend_id=iAPOIP1.vend_id
    			and iAPOIP.inv_no=iAPOIP1.inv_no
    			and iAPOIP.line_no=iAPOIP1.line_no
    	where	iAPOIP1.disb_no=iAPOIP.MaxDisbNo
    	) iAPOIP2 on
    		iAPOIP2.cmpny_cd=tAPOID.cmpny_cd
    		and iAPOIP2.vend_id=tAPOID.vend_id
    		and iAPOIP2.inv_no=tAPOID.inv_no
    		and iAPOIP2.line_no=tAPOID.line_no
    where	tSJ1.UniqueID=' + char(39) + @UniqueID + char(39) + '
    	and tSJ1.show<>0
    order
    by	tSJ1.job_no,
    	case isnull(tSMTCH.[name],' + char(39) + char(39) + ')
    		when ' + char(39) + char(39) + ' then ' + char(39) + ' No Tax Status Code Assigned' + char(39) + '
    		else tAPOID.local_cd+' + char(39) + ' - ' + char(39) + '+tSMTCH.[name]
    	end,
    	tAPOID.inv_no,
    	tAPOID.line_no
    '
    --print @SQL
    exec(@SQL)
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Jan 2010
    Posts
    18
    I wouldn't have a separate column for another language. I would have all of the text in a table with the language code as a part of the key. Then write a function that retrieves the correct text based upon the key.

    For example.

    Product table data

    ProductCode Language Text

    APPLE EN Apple
    APPLE FR Pomme

    So you call the function with the ProductCode of APPLE and Language of FR and the function gives you Pomme (sorry, I don't have any German).

    It really is simple.

    And if your application/business does spread to other countries then it's soooo easy to add another language code.


  6. #6
    Join Date
    Feb 2010
    Posts
    75
    Thanks! I will try this today hopefully.

    I didnt want to build the entire query into a string, just the one table name that is unknown until runtime. But it seems I have no choice but to build the entire query (?)

    It seems that exec() and sp_executesql() are 2 different things, with the latter being the better choice apparently.

    rayqsl, I thought of that solution, and its great in the general case, but I'm sure enough that my app will only require 2 languages, so I thought I could take some shortcuts because of that knowledge.
    Out of curiosity, how would you use your solution in practice? Would you create a SQL function that takes the stringID and languageID as parameters and returns a scalar string value? If so, could you then use that function in a stored procedure like this:

    Code:
    select ID, GETLANG(FRUIT, 'EN') from SomeTable
    Would the above syntax even work? If so, that is pretty slick.
    I would expect that code to return a table like this:

    1 APPLE
    2 BANANA
    3 ORANGE
    ....

  7. #7
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Concerning "It seems that exec() and sp_executesql() are 2 different things, with the latter being the better choice apparently."

    I am always suspicious of some of these internal executables.

    Using the EXEC methodology, I can write my stored procedure, essentially, in three statements:

    1) declare a storage variable for the SQL statement.
    2) construct the SQL statement and assign it to that variable.
    3) execute the string variable.


    If you ever look as some of the built-in stored procedures from Microsoft, they sometimes use hundreds, if not thousands of lines of code to execute something that I can write in one line. Yes, they protect against certain things that I am not protecting against, but I have to tell you that most, if not all of those things are things that I can control in other ways.

    If you read the sp_executesql() manual pages it specifically says that every execution requires a recompilation. It does say that it is possible that a subsequent invocation of the same string, but with different parameters, may match an earlier execution plan, which would make execution using sp_executesql() more efficient.

    But it may be six of one, half-a-dozen of the other.

    In general, if I have a choice of running something that I know requires exactly three execution steps, or running something that has some unknown number of execution steps (possibly thousands), I opt for the animal I do know, not the animal I know very little about.

    Just some philosophy.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  8. #8
    Join Date
    Feb 2010
    Posts
    75
    I definetly agree with your philosophy.

    Here is the article that made me say that sp_executesql is superior:
    SQL SERVER – 2005 Comparison SP_EXECUTESQL vs EXECUTE/EXEC Journey to SQL Authority with Pinal Dave

    Some of the stuff was over my head but what I gleamed was that sp_executesql has
    - parameters, sql-injection prevention, cached query plan, better readability(?)

    I would probably use sp_executesql, and if my application slowed to a crawl because of it, I would look into exec().

  9. #9
    Join Date
    Jan 2010
    Posts
    18
    Sorry - this is in Oracle. It's been a while since I did MSSQL.


    CREATE FUNCTION "GetProductString" (varProductCode in varchar2, varLanguage in varchar2 DEFAULT 'EN') RETURN varchar2 is

    varProductString varchar(256):='Product String not found';

    BEGIN

    SELECT ProductText
    INTO varProductString
    FROM ProductTable
    WHERE ProductCode = varProductCode
    AND Language = varLanguage;

    RETURN varProductString;

    END;

    In your development language, call the function. In T-SQL, it's something like

    declare @ProductString nvarchar(256)

    set @ProductString = select dbo.GetProductString('APPLE','FR')


    Hope this makes sense.

  10. #10
    Join Date
    Feb 2010
    Posts
    75
    Hey guys!
    I just want to say that I wasnt really satisfied with any of the answers (my fault though, I probably didnt explain what I wanted good enough).

    However I figured out a solution that is pretty much exactly what I was looking for, and I just wanted to show you guys what I was after.

    Code:
    Select 
     ID,
     'DESCRIPTION' = CASE @LANG
      WHEN 'en' THEN Description_EN
      ELSE Description_DE
     END,
     Quantity
    From
     FRUIT
    Using that code, I can call a stored procedure, and pass in a language string (either 'EN' or 'DE'), and the procedure will return a column called DESCRIPTION with the correct language! Exactly what I asked for!

    How come you guys didnt tell me this?

  11. #11
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    You're joking, right?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  12. #12
    Join Date
    Feb 2010
    Posts
    75
    Please explain which part is funny.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by BeerOclock View Post
    Please explain which part is funny.
    i think it's this part --
    "my real question is the following: How can I turn a string into an actual column name in T-SQL?"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2010
    Posts
    75
    If that quote was the entirety of my post I could see your point. But I think I did a good job of explaining what I was trying to do, and that line you quoted was my best attempt so far, not necessarily the final solution..

    Besides, the solution I found actually does "turn a string into an actual column name" - it turns 'EN' into Description_EN.

    The reason for my follow up post was just because my solution seems 100 times easier than what others were suggesting, and I wanted to make sure I wasnt missing something, like is it much slower to do it my way, or something like that.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think, sadly, it is one of those cases where you just weren't able to clearly express what you wanted.
    "I have two columns and I want to display the data from either one column or the other dependent on the value of a variable" would have had it cracked within seconds, with the solution you came up with.

    And no, your solution doesn't turn strings in to column names.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •