using the while loop to insert/delete data

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 2000 Forums

Transact-SQL (2000)

using the while loop to insert/delete data

Author

Topic

littlejessihaha
Starting Member

5 Posts

Posted - 2004-10-28 : 06:05:47

Hi There. I have spent several hours trawling the forums to learn how to write a stored procedure. Well I did it (hooray) but I think that my SP could be drastically improved by using a loop. This is something I have never done before and I need help!!

OK - my stored procedure stores records in an archive table and then deletes them from the live table. The records are 'completed jobs' and I send a job number to the stored procedure to achieve this.

Back on the ASP page I select all job numbers which have been competed and are 7 days old using this SQL:

SELECT job_no FROM dbo_jobno_tbl WHERE jobst=4 AND crdate<={fn Now()}-7

Then I LOOP on the ASP page and call my stored procedure many times.
But what if I called my stored procedure only once on the ASP page and did the looping in the stored procedure? How good would that be?

Here is the code for my stored procedure.
Thanks, Jessi:

CREATE PROCEDURE sp_archiveCompletedJobs
@job_number int
AS

set nocount on

--declare variables for task
declare @jobno int
declare @crdate datetime
declare @notes varchar
declare @report varchar
declare @jobst int
declare @jobtype int
declare @howinformed char
declare @allocatedate datetime
declare @tradesman int
declare @invm int
declare @invmpay int
declare @stdate datetime
declare @ecdate datetime
declare @acdate datetime
declare @priority int
declare @keyno float
declare @printflag int
-- check if the record has not already been archived
if not exists (select jobno from dbo_arch_jobno_tbl where jobno=@job_number)
begin

-- select record data from live db table
SELECT @jobno=jobno, @crdate=crdate, @notes=notes, @report=report, @jobst=jobst,
@jobtype=jobtype, @howinformed=howinformed, @allocatedate=allocatedate,
@tradesman= tradesman, @invm=invm, @invmpay=invmpay, @stdate=stdate, @ecdate=ecdate,
@acdate=acdate, @priority=priority,@keyno=keyno,@printflag=printflag
FROM dbo_jobno_tbl WHERE jobno=@job_number;

--insert into archived db table
INSERT INTO dbo_arch_jobno_tbl
(jobno, crdate, notes, report, jobst, jobtype, howinformed, allocatedate,
tradesman, invm, invmpay, stdate, ecdate, acdate, priority, keyno,
printflag, arch_date)
VALUES
(@jobno, @crdate, @notes, @report, @jobst, @jobtype, @howinformed, @allocatedate,
@tradesman, @invm, @invmpay, @stdate, @ecdate, @acdate, @priority, @keyno,
@printflag, {fn now()});

end

if exists (select jobno from dbo_jobno_tbl where jobno=@job_number)
begin
/*This record now exists in the archives.
Some records have been archived but not deleted from the
--live db table. Delete these records*/
DELETE dbo_jobno_tbl WHERE jobno=@job_number;
end

--------------------
Well that's it. Can I insert the SQL: SELECT job_no FROM dbo_jobno_tbl WHERE jobst=4 AND crdate<={fn Now()}-7 and then loop through that recordset archiving away as I do?

xx

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-10-28 : 06:59:50

Hi
You dont need all those variables you could do all this in one stored procedure and excecute it from a job daily to automatically archive your data

Something like
CREATE PROCEDURE sp_archiveCompletedJobs
AS

BEGIN TRANSACTION
--Insert into archive
INSERT dbo.arch_jobno_tbl (jobno, crdate, notes, report, jobst, jobtype, howinformed, allocatedate, tradesman, invm, invmpay, stdate, ecdate, acdate, priority, keyno, printflag, arch_date)

SELECT jobno, crdate, notes, report, jobst, jobtype, howinformed, allocatedate, tradesman, invm, invmpay, stdate, ecdate, acdate, priority, keyno, printflag, GETDATE()
FROM dbo.jobno_tbl
WHERE jobst=4 AND DATEDIFF(dd,crdate,GETDATE()) = 7

--Delete from live table
DELETE dbo.jobno_tbl
FROM dbo.jobno_tbl
WHERE jobst=4 AND DATEDIFF(dd,crdate,GETDATE()) = 7
COMMIT

PS - Its not good practice to start stored procedures with sp_ as this is what MS use for system stored procedures

BOL

quote:
The visibility for stored procedures that begin with sp_ differs from the visibility for regular stored procedures

Andy

littlejessihaha
Starting Member

5 Posts

Posted - 2004-10-28 : 08:46:53

Hi Andy,

Will that code insert all the records which need to be archived in one go then? I didn't know it was that simple. Thank you.

My code is actually much more complex than the example I gave because it involves several tables but I simplified it to give the general impression. I have to use several IF statements too. However I think I can apply the principle.

Just out of interest is it possible to do this as a looping statement?

Jessi x

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-10-28 : 08:57:36

Yes that will insert ALL records that have jobst=4 and a difference in days between crdate and today equal to 7

It then uses the same "WHERE" statement to delete the records

You could loop using a cursor but it isnt necessary or recomended

Andy

littlejessihaha
Starting Member

5 Posts

Posted - 2004-10-28 : 09:05:21

Great.

I am only just getting into the realm of SQL where you actually have to stop and think (ie. beyond basic SELECT, INSERT and UPDATE statements) but already I understand that cursors are not the way to go if they can be avoided. Thanks for your solution, I will adapt it to my longer code and then give give it a test.

Jessi

xx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources