April 8, 2004 at 8:22 am
I created the following code to create a procedure that will update records from an AS400 linked table to a SQL table. It also gets information from 2 other AS400 table. I think if the 3 AS400 tables can be joined, the procedure will run faster. Any other recommendation is also appreciated. Being a newbie, I appreciate any help!
here is the code:
CREATE PROCEDURE dbo.SalespersonDimensionUpdate AS
DECLARE
@Exist char(3),
@Salesperson_Number char(3),
@Salesperson_Name char(30),
@Salesperson_Group_Type char(2),
@Salesperson_Group_Name char(30),
@Salesperson_Group_Territory char(10),
@Division_Code char(3),
@Division_Name char(45)
DECLARE prod_cursor CURSOR FOR SELECT SALCOD, SALNAM, SALTYP, SALDIV FROM CORP400.CORP400.TURDTA.PSALTAB
OPEN prod_cursor
FETCH NEXT FROM prod_cursor INTO
@Salesperson_Number,
@Salesperson_Name,
@Salesperson_Group_Type,
@Division_Code
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Division_Name = (SELECT DIVNAM FROM CORP400.CORP400.TURDTA.PDIVTAB WHERE DIVCOD = @Division_code)
SET @Salesperson_Group_Name = (SELECT SGRNAM FROM CORP400.CORP400.TURDTA.PSGRTAB WHERE SGRCOD =@Salesperson_Group_Type)
SET @Salesperson_Group_Territory = (SELECT SGRTER FROM CORP400.CORP400.TURDTA.PSGRTAB WHERE SGRCOD =@Salesperson_Group_Type)
SET @Exist = (SELECT Salesperson_Number FROM Salesperson_Dimension WHERE Salesperson_Number = @Salesperson_Number)
if @Exist is null
begin
insert into Salesperson_Dimension (
Salesperson_Number, Salesperson_Name,
Salesperson_Group_Type,
Division_Code,
Division_Name,
Salesperson_Group_Name,
Salesperson_Group_Territory)
VALUES (
@Salesperson_Number,
@Salesperson_Name,
@Salesperson_Group_Type,
@Division_Code,
@Division_Name,
@Salesperson_Group_Name,
@Salesperson_Group_Territory)
end
else
begin
update Salesperson_Dimension set
Salesperson_Name = @Salesperson_Name,
Salesperson_Group_Type = @Salesperson_Group_Type,
Division_Code = @Division_Code,
Division_Name = @Division_Name,
Salesperson_Group_Name = @Salesperson_Group_Name,
Salesperson_Group_Territory = @Salesperson_Group_Territory where Salesperson_Number = @Salesperson_Number
end
FETCH NEXT FROM prod_cursor INTO
@Salesperson_Number,
@Salesperson_Name,
@Salesperson_Group_Type,
@Division_Code
END
CLOSE prod_cursor
DEALLOCATE prod_cursor
GO
April 8, 2004 at 9:15 pm
0. At top, between AS and DECLARE, add line with "set nocount on"
1. cursor declaration: depending on your coding stds, definitely add either "FOR READ ONLY" (ANSI) or FAST_FORWARD (TSQL). I'd also try either INSENSITIVE (ANSI) or STATIC (TSQL) and see if this helped or not. Probably only have to test each way once to see which works better.
2. Unless you use insensitive or static cursor, need to watch out for @@fetch_status = -2, else you may stop traversing the cursor prematurely. My generic cursor loop to handle this is
open prod_cursor
while (1 = 1)
begin
fetch prod_cursor into ...
if @@fetch_status = -1
break
if @@fetch_status = -2
continue
...
end
deallocate prod_cursor
3. No need to close cursor anymore, unless you're going to re-open it. Just deallocate is fine.
4. you can combine 2 selects into one, but not sure about the other tables:
select @Salesperson_Group_Name = SGRNAM,
@Salesperson_Group_Territory = SGRTER
from CORP400.CORP400.TURDTA.PSGRTAB
where SGRCOD = @Salesperson_Group_Type
5. Instead of "Set @Exist ... if @Exist is null"
if exists(SELECT Salesperson_Number from ...)
insert Salesperson_Dimension ...
else
update Salesperson_Dimension ...
6. After insert/update you probably want to check @@error? Would you do anything differently if you knew 1/2 of the insert/updates failed?
select @err = @@error
if (@err <> 0) ... do something ...
April 19, 2004 at 8:02 am
Thanks Mike,
Your suggestions was just what I needed...thanks for your help...
Regarding to do something on Error, how would you code the process to write the error information into a txt file?
Thanks again for your help!
April 19, 2004 at 10:27 am
First need to fix previous post para #5 "if exists" to "if not exists" ... that's pretty embarassing.
I have never explicitly tried to log SQL errors directly into a text file. I'm sure it's doable, and you could probably get lot of advice if you posted a new question on this.
What I have done is write important errors to SQL's error log with RAISERROR ... WITH LOG. These messages are also written to the OS' application event log, and in just a few mins you should be able to download MS LogParser utility and configure it to scan the event log & generate textfile you're looking for.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply