Eliminiating Repeating logic? - 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

General SQL Server Forums

New to SQL Server Programming

Eliminiating Repeating logic?

Author

Topic

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-07-16 : 18:07:52

I currently have a working sql 2000 program (SOEwkly.sql) which is made up of 3 steps. I basically need to run this program 30+ times. The only thing that changes each time is the table name it reads from and then save the tablename to a field in step1 (see green and red below).

I am not sure if or how this can be done. Can someone help me with the coding? Thanks.


------------------------------------------
-- Step 1:  Creates ByProduct  table
------------------------------------------

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'ByProduct')
		and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  Begin
     create table ByProduct (Type char(20), Address char(50), 
     SumOfNotional float, SumOfAvgBal float)
     CREATE INDEX  CustID_IDX on CustBaseCase(AccountID)
   End

ELSE
	Truncate TABLE ByProduct

------------------------------
-- Step2:
------------------------------
Create table ...
.
.
.

--------------------------------------------------------
-- Step3:  Performs match b/w 2 files & inserts sum of values to CustBaseCase table 
--------------------------------------------------------
INSERT INTO ByProduct(Type, Address,city,SumOfNotional, SumOfAvg)

SELECT  'CUSTBaseCase' as Type, 
Address, City,
sum(s.notional) as SumOfNotional,
sum(s.avgbal) as SumOfAvg
from Parsed as w   inner JOIN dbo.CustBaseCase as S
on w.[w_id]=s.accountid
group by type,city

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-17 : 02:27:19

you can create a dynamic sql query using while loop to repeat this step 30 times and each time input parameters (table name, etc) are different.

second option is to create a procedure where again input parameter is name of the table and you copy paste only a single row:
execute sp_create_table @tableName = 'CustBaseCase1'
execute sp_create_table @tableName = 'CustBaseCase2'
etc...

the second option is faster because you copy only 1 row 30 times and change the parameter.

but if you will be using this query often i suggest you to write dynamic query to avoid potential typos and errors. If it's only one time, create procedure and copy execute code 30times.

cirugio
Yak Posting Veteran

90 Posts

Posted - 2010-07-17 : 20:29:04

Sorry, I am fairly new to sql, so a bit confused on how to go about this. I will need to run this particular program weekly and really do not want to type in the parameters each time in fair of typos.

I am not exactly sure how to write a dynamic query or what it really is. Can you provide me some additional guidance. I am currently using sql 2000.

Also, if I was to create a stored procedure, did I understand correctly that I would need to just have a stored procedure with 30 lines of codes in it? If I took this route how would I modify the program to read the @tablename parameter? Where do I define @tablename? and How can I get @tablename to be written into a field in the first step so it is stored into the table? My apologies, but I still trying to get a handle on all this and a bit unclear. Regards, cirugio

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources