December 10, 2002 at 7:59 am
I have a requirement to provide translated information into various applications and I want to create re-useable code in an SP or UDF, but so far I have been unable to define this. Can anyone offer any assistance.
Example tables
The MAIN Table
tblApplication
AppID int
AppName varchar(50)
PopupText varchar(500)
has a name and popuptext to be translated
if the translation exists in the table
tblAppTranslation
Appname varchar(50)
LanguageID int
Description varchar(50)
PopupText varchar(500)
I need to pass in the tablename, language and appname and if a record exists in the translation table, then the Description and Popuptext are returned - otherwise, the appname and popuptext are returned from the main table
Ian Dallow
December 10, 2002 at 8:06 am
Since you are looking to return 2 values, I would suggest the use of an SP with 2 OUTPUT params rather than a UDF.
December 10, 2002 at 8:10 am
What popuptext are do you want returned when the tblapptranslation does not have have a matching language and appname?
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 10, 2002 at 8:13 am
The Popuptext required if there is no record in the Translation table is the one from the Main table record
December 10, 2002 at 8:16 am
We created tables and views to solve this problem.
create table Phrase
(PhraseID int (PK)
PhraseName varchar(50)
)
create table LocalizedPhrase
(PhraseID int (pk, FK)
LanguageID int (pk)
Phrase varchar(3000)
)
We have a view that joins the localized phrases with the english language phrase so we are ensure of getting something back.
The app maintains the phrase list. We use an external $ervi¢e to translate phrases to multiple languages.
December 11, 2002 at 3:14 am
The main table will not necessarily hold englisg text. The text will be in the local language of the user who inserted the record. There will always be a pair of text fields available, but not necessarily in the translation language requested.
The required results are :
For
Pass in parameters of @tablename @entity and @language
MAIN TABLE described as
TABLENAME=tblMain
EntityID1 int
Description1
PopupText1
TRANSLATION TABLE described as
TABLENAME = tblMainTranslation
EntityID2 int
Description2
PopupText2
SELECT [Description2],PopupText2 FROM @tablenameTranslation WHERE EntityID2 = @entity AND languageID = @languageI
If no record found then
SELECT [Description1],Popuptext1 FROM
@tablename WHERE EntityID1 = @entity
There are many pairs of associated tables that are populated through application code.
I am trying to find a way of having a single code object that can be parameterised to handle translation in a common way
December 11, 2002 at 4:27 am
Try this using a Stored Procedure
if exists (SELECT [Description2],PopupText2 FROM @tablenameTranslation WHERE EntityID2 = @entity AND languageID = @languageI)
begin
SELECT [Description2],PopupText2 FROM @tablenameTranslation WHERE EntityID2 = @entity AND languageID = @languageI
end
else
begin --If no record found then
SELECT [Description1],Popuptext1 FROM
@tablename WHERE EntityID1 = @entity
end
If this is part of an application return you might try the suggestion of using two output parameters so you can avoid a record set. Otherwise the above syntax should do what you want.
December 11, 2002 at 6:17 am
Thanks for that.
Right I've now got the following statements to return the correct information in a grid.
Can anyone advise how to capture the returned data into output parameters ?
I am getting errors regarding 'must declare variable' if I use SELECT @outparm=[Description] within the selectstring parameter.
December 11, 2002 at 6:19 am
And here are the statements :-
DECLARE @tablename char(30)
DECLARE @languageIDint
DECLARE @entityID int
SET @tablename='tblIANMain'
SET @languageID=64
SET @entityID=1
DECLARE @selectstring nvarchar(500)
SET @selectstring='SELECT [Description],PopupText FROM EDB.dbo.' +
rtrim(ltrim(@tablename)) + 'Translation WHERE EntityID = ' + cast(@entityID as char(2)) +
' AND LanguageID = ' + cast(@languageID as char(2))
exec (@selectstring)
if @@rowcount=0
BEGIN
SET @selectstring='SELECT [Description],PopupText FROM EDB.dbo.' +
rtrim(ltrim(@tablename)) + ' WHERE EntityID = ' + cast(@entityID as char(2))
exec (@selectstring)
END
December 11, 2002 at 6:41 am
Try this for the procedure side.
CREATE PROCEDURE usp_GetDesAndPopup
@tablename sysname,
@languageID int,
@entityID int,
@Descriptionvarchar(100) OUTPUT,
@PopupTextvarchar(100) OUTPUT
AS
SET NOCOUNT ON
DECLARE @sqlstr nvarchar(2000)
SET @sqlstr = 'if exists (SELECT @Description = [Description], @PopupText = PopupText FROM EDB.dbo.' + @tablename + ' Translation WHERE EntityID = @entityID AND LanguageID = @languageID)
SELECT @Description = [Description], @PopupText = PopupText FROM EDB.dbo.' + @tablename + 'Translation WHERE EntityID = @entityID AND LanguageID = @languageID
else
SELECT @Description = [Description], @PopupText = PopupText FROM EDB.dbo.' + @tablename + ' WHERE EntityID = @entityID'
EXEC sp_executesql @sqlstr, N'@languageID int, @entityID int, @Description varchar(100) OUTPUT, @PopupText varchar(100) OUTPUT', @languageID, @entityID, @Description OUTPUT, @PopupText OUTPUT
December 11, 2002 at 6:57 am
When I run this as a procedure
declare @out1 varchar(50)
declare @out2 varchar(300)
exec usp_GetDesAndPopup tblIANMain,64,1,@out1,@out2
it gives errors
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'else'.
sql does not seem to like the @description=[description] syntax within the built string
As a single non-parameterised sql statement it works fine
December 11, 2002 at 7:50 am
I believe all you are missing is the OUTPUT keyword with the parameters that are accepting OUTPUT. But try this.
declare @out1 varchar(50)
declare @out2 varchar(300)
exec usp_GetDesAndPopup @tablename = 'tblIANMain', @languageID = 64, @entityID = 1, @Description = @out1 OUTPUT, @PopupText = @out2 OUTPUT
December 11, 2002 at 8:16 am
I've just run the amended statement, but this still returns the same errors
December 11, 2002 at 8:31 am
I am such an idiot here is the problem and it is all my fault.
Change this
SET @sqlstr = 'if exists (SELECT @Description = [Description], @PopupText = PopupText FROM EDB.dbo.' + @tablename + ' Translation WHERE EntityID = @entityID AND LanguageID = @languageID)
SELECT @Description = [Description], @PopupText = PopupText FROM EDB.dbo.' + @tablename + 'Translation WHERE EntityID = @entityID AND LanguageID = @languageID
else
SELECT @Description = [Description], @PopupText = PopupText FROM EDB.dbo.' + @tablename + ' WHERE EntityID = @entityID'
to
SET @sqlstr = 'if exists (SELECT [Description], PopupText FROM EDB.dbo.' + @tablename + ' Translation WHERE EntityID = @entityID AND LanguageID = @languageID)
SELECT @Description = [Description], @PopupText = PopupText FROM EDB.dbo.' + @tablename + 'Translation WHERE EntityID = @entityID AND LanguageID = @languageID
else
SELECT @Description = [Description], @PopupText = PopupText FROM EDB.dbo.' + @tablename + ' WHERE EntityID = @entityID'
Sorry about that.
December 11, 2002 at 8:52 am
Thanks for that -
it's now complaining about an invalid column
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'LanguageID'.
This column is correctly defined in tblIANMAinTranslation table
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply