January 18, 2008 at 6:17 am
Hi All,
I would like to get some advice / Help from all of you out there.
I have four tables namely
tbEnglish, tbFrench, tbChinese and tbJapanese.
They have two columns. One is Base Varchar(50) and the other is Text (For English, Frc for french, Chi for Chinese) and the Datatype is Text.
Based on a variable (Variable defines from which table I have to fetch data from) I have to get data. What I want to do is to write a stored Proc that fetches the data from one of these tables based on the variable passed to it. I want to know if there is any way I can do it without using Dynamic SQL.
I know I can do it by doing some IF statements. Is there any other way?
Thanks
Roy
-Roy
January 18, 2008 at 6:44 am
just my 2ct.
- avoid dynamic sql
- add a language code column an just use one table
- maybe even partition on the language code column
- if you still want tables to be separated, still add the language code and use a view that joins the x tables
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 18, 2008 at 6:54 am
There are two ways I know of to do this efficiently:
select Base, Text
from tbEnglish
where @Lang = 'English'
union all
select Base, Chinese
from tbChinese
where @Lang = 'Chinese'
...(rest of tables)...
if @Lang = 'English'
select Base, Text
from tbEnglish
else if @Lang = 'Chinese'
select Base, Chinese
from tbChinese
else if ...(rest of tables)...
Test both and see what kind of execution plan SQL comes up with and what kind of speed you get.
The Union All solution has the advantage that the column names returned are always the same, but you can fix that in the If...Else If... solution with column aliases.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 18, 2008 at 7:07 am
ALZDBA
I do not want to use Dynamic SQL. I know it is powerful but I have never been comfortable using it. Unfortunately these tables has been there for years and I cannot modify them.
GSquared
I did not think about the Union All Option. Thats something I can try. I will compare it with the other IF Option. The thing I do not like about the IF is it could cause recompile.
Thanks to both of you for your input. Appreciate it.
-Roy
January 18, 2008 at 7:29 am
I wonder how intelligent SQL2005 would handle a this
create view V_myLanguages
as
select 'English' as Language, Base, Text
from tbEnglish
select 'French' , Base, Text
from tbFrench
select 'Chinese' , Base, Text
from tbChinese
go
Select ...
from V_myLanguages
where Language = @Lang -- = 'English'
Check the queryplan !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 18, 2008 at 8:03 am
Thats another logical option. Let me check the query plans for all these options.
You guys have given me 2 additional option than what I had.
Thx:)
-Roy
January 18, 2008 at 8:14 am
ALZDBA (1/18/2008)
I wonder how intelligent SQL2005 would handle a thiscreate view V_myLanguages
as
select 'English' as Language, Base, Text
from tbEnglish
select 'French' , Base, Text
from tbFrench
select 'Chinese' , Base, Text
from tbChinese
go
Select ...
from V_myLanguages
where Language = @Lang -- = 'English'
Check the queryplan !
A view definition is composed of a SINGLE select statement. I'm pretty sure that won't ever create the view, unless you UNION ALL the statements (like has been previously mentioned).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 18, 2008 at 8:20 am
ALZDBA (1/18/2008)
I wonder how intelligent SQL2005 would handle a thiscreate view V_myLanguages
as
select 'English' as Language, Base, Text
from tbEnglish
select 'French' , Base, Text
from tbFrench
select 'Chinese' , Base, Text
from tbChinese
go
Select ...
from V_myLanguages
where Language = @Lang -- = 'English'
Check the queryplan !
You need to add Union or Union All statements between the selects, or the view won't compile. Will give:
"Msg 156, Level 15, State 1, Procedure V_myLanguages, Line 5
Incorrect syntax near the keyword 'select'."
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 18, 2008 at 8:22 am
Matt Miller (1/18/2008)
... A view definition is composed of a SINGLE select statement. I'm pretty sure that won't ever create the view, unless you UNION ALL the statements (like has been previously mentioned).
that's off course a typo from my side ... I think Roy got the picture.
Human after all 😉
-- use UNION ALL because you want all rows from each individual select.
create view V_myLanguages
as
select 'English' as Language, Base, Text
from tbEnglish
UNION ALL
select 'French' , Base, Text
from tbFrench
UNION ALL
select 'Chinese' , Base, Text
from tbChinese
go
Select ...
from V_myLanguages
where Language = @Lang -- = 'English'
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 18, 2008 at 8:32 am
ALZDBA, Yes I did get the picture. The only thing I was concerned about was the column name. But that shouldnt be a big issue. I think I can get the App to handle it.
Thx guys/Ladies. 🙂
-Roy
January 18, 2008 at 8:47 am
ALZDBA (1/18/2008)
Human after all 😉
yup - right there with ya!!!!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply