Using a parameter table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using a parameter table

Author

Topic

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2008-10-22 : 15:58:24

Hi all,

I wonder whether this is the best solution, but at least it works: I have a table containing several numerical values. On my webpage I want to display the synonym for it, e.g.: 1 means 'excellent', 10 means 'worsest'. Instead of 'translating' the numerical values into text in my webapp I've now created several parametertables in SQL2005, hoping that this will be faster than on the IIS. So my SP looks like this:


SELECT PS.name, 
	PS.Leadership, PLead.Skillname as LeadershipName,
	PS.Availability, PAvail.Skillname as AvailabilityName,
	PS.Employment, PEmp.Skillname as EmploymentName,
	PS.CountryID,
	WD.CountryName
FROM Statics PS 
INNER JOIN WorldDetails WD ON PS.CountryID = WD.CountryID
INNER JOIN ParameterSkill PLead on PS.Leadership = PLead.SkillLevel AND PLead.LanguageCode = @LanguageCode
INNER JOIN ParameterAvailability PAvail on PS.Availability = PAvail.SkillLevel AND PAvail.LanguageCode = @LanguageCode
INNER JOIN ParameterEmployment PEmp on PS.Employment = PEmp.SkillLevel and PEmp.LanguageCode = @LanguageCode
WHERE UserID = @UserID

Ah, and I want to have it multilanguage in the future, so I already have a language code (e.g. 'en') included.

Is this ok or is there a better solution?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-22 : 16:42:53

There perhaps is a better solution, however we don't have enough information to help you. Show us some sample rows from each of the tables.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2008-10-22 : 17:08:54

Hi Tara,

ok, no problem:
The parametertables (ParameterSkill, ParameterAvailability, ParameterEmployment) all have the same structure:

LanguageCode - SkillLevel - Skillname
en             1            disastrous
en             2            poor
en             3            weak
en             4            solid
...

Of course the values of 'Skillname' differs, but 'LanguageCode' and 'SkillLevel' (normally 0 to 20) is the same for each parametertable (but of course they are not connected to each other, so you could have Skill 3 and Availability 8 or so).

The Statics table then only contains the numbers

UserID - Name - Leadership - Availability - Employment - CountryID
1        Joe    2            7              16           17
2        Paul   4            1              7            22

My WebApp should not display 'Leadership = 2' but 'Leadership = poor'. I'm not sure where to calculate the real names of the skills, I thought it would be the fastest (and also quite easy to maintain, also for multi-language) to have it already on the SQL-Server so my app only needs to display the retrieved values.

UserID - Name - Leadership - LeadershipName - Availability - AvailName - Employment - EmployName - CountryID - CountryName
1        Joe    2            poor             7              70-80%      16           25-30 years  17          Albania
2        Paul   4            solid            1              < 10%       7            7 years      22          Bulgaria

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 00:32:06

your solution seems to a good solution for scenario explained. The only overhead is maintain the values in maping table, but thats much easier compared to hardcoding of value in your retrieval queries.

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2008-10-23 : 07:58:09

Yes, especially when I want to add several other languages

I wasn't sure about have multiple (in the end maybe 10) inner joins in a single statement, but I guess that's still an easy one (not time-consuming) for the SQL Server, right?

One additional question: When you look at the SELECT in the first post: What if I have a second column which should retrieve the value from a table I have already joined? See the new lines in bold

SELECT PS.name, 
	PS.Leadership, PLead.Skillname as LeadershipName,
	PS.TeamSpirit, PTeam.Skillname as TeamSpiritName,
	PS.Availability, PAvail.Skillname as AvailabilityName,
	PS.Employment, PEmp.Skillname as EmploymentName,
	PS.CountryID,
	WD.CountryName
FROM Statics PS 
INNER JOIN WorldDetails WD ON PS.CountryID = WD.CountryID
INNER JOIN ParameterSkill PLead on PS.Leadership = PLead.SkillLevel AND PLead.LanguageCode = @LanguageCode
INNER JOIN ParameterSkill PTeam on PS.TeamSpirit = PTeam.SkillLevel AND PTeam.LanguageCode = @LanguageCode
INNER JOIN ParameterAvailability PAvail on PS.Availability = PAvail.SkillLevel AND PAvail.LanguageCode = @LanguageCode
INNER JOIN ParameterEmployment PEmp on PS.Employment = PEmp.SkillLevel and PEmp.LanguageCode = @LanguageCode
WHERE UserID = @UserID

So Leadership and TeamSpirit take the values from the same SQL-Table ('Parameterskill') but do I need to have 2 inner joins then? Or could / should I combine that into a single inner join?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 08:31:50

quote:
Originally posted by Heinz23

Yes, especially when I want to add several other languages
I wasn't sure about have multiple (in the end maybe 10) inner joins in a single statement, but I guess that's still an easy one (not time-consuming) for the SQL Server, right?

One additional question: When you look at the SELECT in the first post: What if I have a second column which should retrieve the value from a table I have already joined? See the new lines in bold
SELECT PS.name,
PS.Leadership, PLead.Skillname as LeadershipName,
PS.TeamSpirit, PTeam.Skillname as TeamSpiritName,
PS.Availability, PAvail.Skillname as AvailabilityName,
PS.Employment, PEmp.Skillname as EmploymentName,
PS.CountryID,
WD.CountryName
FROM Statics PS
INNER JOIN WorldDetails WD ON PS.CountryID = WD.CountryID
INNER JOIN ParameterSkill PLead on PS.Leadership = PLead.SkillLevel AND PLead.LanguageCode = @LanguageCode
INNER JOIN ParameterSkill PTeam on PS.TeamSpirit = PTeam.SkillLevel AND PTeam.LanguageCode = @LanguageCode
INNER JOIN ParameterAvailability PAvail on PS.Availability = PAvail.SkillLevel AND PAvail.LanguageCode = @LanguageCode
INNER JOIN ParameterEmployment PEmp on PS.Employment = PEmp.SkillLevel and PEmp.LanguageCode = @LanguageCode
WHERE UserID = @UserID
So Leadership and TeamSpirit take the values from the same SQL-Table ('Parameterskill') but do I need to have 2 inner joins then? Or could / should I combine that into a single inner join?

you do need two joins. but if you've lots of value to be retrieved like this from table, consider adding value column wise to table rather than row wise.
ie you wont add fields like Leadership,TeamSpirit,.. instead you will have a generic column type, SkillLevel & Skillname

then take it in cross tabed forum for joining. values will be like

type skilllevel skillname
leadership 1 disastrous
leadership 2 poor
....
teamspirit 1 poor
...
teamspirit 5 team player
...

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2008-10-23 : 09:33:20

Hi Visakh16,

not sure what you mean.... As I'm only using it for 1 User ('Where UserID = @UserID') I guess it's ok as it is, right? Your advice would be for the case that I call this SP for all employees, so for hundreds of rows? But what means

quote:
then take it in cross tabed forum for joining.

? I create a new table which I also return so that my webapp should make the combining / join? Or I load the data into a TempTable and do the InnerJoin then there?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 09:50:55

quote:
Originally posted by Heinz23

Hi Visakh16,

not sure what you mean.... As I'm only using it for 1 User ('Where UserID = @UserID') I guess it's ok as it is, right? Your advice would be for the case that I call this SP for all employees, so for hundreds of rows? But what means
quote:
then take it in cross tabed forum for joining.
? I create a new table which I also return so that my webapp should make the combining / join? Or I load the data into a TempTable and do the InnerJoin then there?

sorry you missed my point. i was not talking about number of users but i was telling an alternative to reduce number of joins. each time you want a skillname of new category(leadership,teamspirit..) you need an extra join with table under present condition. what i was suggesting was a way to reduce joins. the strategy is to keep the current categories as it as a column in new table and represent values as rows rather than columns as shown earlier. this ensures whenever you want to join for a category just cross tab the current table and join with appropriate column.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-10-23 : 12:56:04

not to derail your topic, but if you are using ASP then it has a pretty rich set of localization functionality built into it. Another option you might want to explore.

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2008-10-24 : 09:10:39

Hi visakh16,

ok, thanks for further explanation. I'm not sure whether I understood correct, additionally I would definitely be able to write such a query on my own. If you have some URL references or a short example I would be grateful, otherwise I don't want to waste your time and as the current query also works fine I'm already happy with that, so thanks for all your advices.

Hi Lamprey,
yes, I know the localization options in ASP.net and I'll definetely use them, so the topic was more about the connection between skill and skillname, and multilanguage was just on top for explanational reasons. As I already need to use a sql parameter table I think it's best to have it already multilanguage there as it prevents further steps (and time) in asp.net. Anyway thanks for the advice.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources