Using SP, UDF etc for table joins

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

General SQL Server Forums

New to SQL Server Programming

Using SP, UDF etc for table joins

Author

Topic

oldfox
Starting Member

17 Posts

Posted - 2010-09-28 : 17:47:30

Hi all
Is there a way to wright same procedure or udf etc that can be applied on different tables?

For example I have table with items list and several tables with prices.
I assign prices using joins and cross applies.
So if I have 10 different price lists, I have to copypaste code 10 times and if I need to change the code, I have to do it in many places, that may lead to errors.
Is there a way to write a code once as a procedure, udf etc and be able to use in on different tables?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-28 : 17:52:14

only if you use dynamic sql and accept the table name as a paramater.

or you could have a huge if/else based on the param value, but i wouldn't recommend that.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-28 : 17:55:37

Please post a query example to make this more clear.

Have you considered using views?

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

Subscribe to my blog

oldfox
Starting Member

17 Posts

Posted - 2010-09-28 : 18:17:26

I have 10-20 rate sheets from 10-20 different companies.
I have my list of country-city codes and I need to assign rates from all companies and I use conditional joins.
for example 5 companies may have same table format and I can use absolutely the same code (with different table names)
And as I have several country-city codes tables (full table, regional tables) I have to copy- paste same code many times using different table names.

drop table #my_list
drop table #phone_company_list

create table #my_list
(cc_prefix numeric,
CityCountry varchar(200))
create table #phone_company_list
(cc_prefix numeric,
CityCountry varchar(200),
rate decimal(10,5))

insert into #my_list values (44,'UK DEFAULT')
insert into #my_list values (4478,'UK MMO2')
insert into #my_list values (4478721,'UNITED KINGDOM MMO2 MOBILE')
insert into #my_list values (44787210,'UNITED KINGDOM MMO2 MOBILE')

insert into #phone_company_list values (44,'UK DEFAULT',0.01)
insert into #phone_company_list values (4478721,'UNITED KINGDOM MMO2 MOBILE',0.02)

select * from #my_list
select * from #phone_company_list

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-28 : 19:05:33

Your database design is flawed.. You need to normalize your schema so that you don't have multiple tables with the same format.

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

Subscribe to my blog

oldfox
Starting Member

17 Posts

Posted - 2010-09-28 : 19:16:21

I receive rate sheets from different companies. They have 2 columns: Country code and Rate
How can I normalize this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-28 : 19:35:50

Add a company id column.

CompanyId, CountryCd, Rate

Then you can store it all in one table.

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

Subscribe to my blog

oldfox
Starting Member

17 Posts

Posted - 2010-09-28 : 19:41:59

But I still need to assign all rates.
The resulting table should look like
Code -- Rate#1 - Rate#2 etc

I still have to write many case statements even when all rates in one table

kevlangdo
Starting Member

5 Posts

Posted - 2010-09-28 : 21:35:07

Create a Stored Procedure and pass the Table object as a parameter. T-SQL Allows to do this.

Kevin Languedoc
Sr BI Developer
www.kcodebook.com/wp

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-28 : 22:45:16

If they are all in one table, then you won't need to use dynamic SQL. Many CASE statements is not a problem. If you post a better data example, then we likely can help you write the best query for it.

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

Subscribe to my blog

oldfox
Starting Member

17 Posts

Posted - 2010-09-29 : 20:34:51

Thanks everybody for answers.
Here is an code as example.
I have several lists of country codes and different rate plans from different companies ( but many rate plans have the same structure and same column names)
It looks simple now, but in reality I use more complex conditional join and I can assign rates for one list of countries today, another list in 2 weeks, third list in 2 month and its really hard to keep track of changes.
This is why I am trying to write a procedure/udf so I can refer to the procedure and just pass table names.
Or to optimize the process in any other ways that I am not aware of :)

DROP TABLE #Countries
CREATE TABLE #Countries
(Code int, Country varchar(20))

INSERT INTO #Countries
SELECT '34', 'Spain'
UNION SELECT '44','UK'
UNION SELECT '45','Denmark'
UNION SELECT '49','Germany'

DROP TABLE #RatePlan1
CREATE TABLE #RatePlan1
(Code int, Rate money)

INSERT INTO #RatePlan1
SELECT '34', '0.02'
UNION SELECT '44','0.01'
UNION SELECT '45','0.03'
UNION SELECT '49','0.015'

DROP TABLE #RatePlan2
CREATE TABLE #RatePlan2
(Code int, Rate money)

INSERT INTO #RatePlan2
SELECT '34', '0.022'
UNION SELECT '44','0.012'
UNION SELECT '45','0.032'
UNION SELECT '49','0.0152'

DROP TABLE #RatePlan3
CREATE TABLE #RatePlan3
(Code int, Rate money)

INSERT INTO #RatePlan3
SELECT '34', '0.033'
UNION SELECT '44','0.013'
UNION SELECT '45','0.044'
UNION SELECT '49','0.0117'

Select c.*, r1.Rate FROM #Countries c
left join #RatePlan1 r1 on
(c.Code=r1.Code)

union all
Select c.*, r2.Rate FROM #Countries c
left join #RatePlan2 r2 on
(c.Code=r2.Code)

union all
Select c.*, r3.Rate FROM #Countries c
left join #RatePlan3 r3 on
(c.Code=r3.Code)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-30 : 13:29:47

I do not see any reason why you can't combine the different rate plan tables into one. It sounds like you just need to add an additional column that would differentiate the rate plans such as a type.

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

Subscribe to my blog

oldfox
Starting Member

17 Posts

Posted - 2010-09-30 : 13:52:54

tkizer, thank you very much for your patiens.
Could you please write a query as an example. If I combine all rate plans into one table, I still don't understand how I can use only one query to get the same result as I have now using several joins.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-30 : 13:58:18

Like this:

CREATE TABLE #Countries
(Code int, Country varchar(20))

INSERT INTO #Countries
SELECT '34', 'Spain'
UNION SELECT '44','UK'
UNION SELECT '45','Denmark'
UNION SELECT '49','Germany'

CREATE TABLE #RatePlan
(Code int, Rate money, Type tinyint)

INSERT INTO #RatePlan
SELECT '34', '0.02', 1
UNION SELECT '44','0.01', 1
UNION SELECT '45','0.03', 1
UNION SELECT '49','0.015', 1

UNION SELECT '34', '0.022', 2
UNION SELECT '44','0.012', 2
UNION SELECT '45','0.032', 2
UNION SELECT '49','0.0152', 2

UNION SELECT '34', '0.033', 3
UNION SELECT '44','0.013', 3
UNION SELECT '45','0.044', 3
UNION SELECT '49','0.0117', 3

Select c.*, r.Rate FROM #Countries c
left join #RatePlan r on
(c.Code=r.Code)

DROP TABLE #Countries, #RatePlan

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

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources