Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Stored Procedure WHILE LOOP syntax

Posted on 2010-09-01
9
Medium Priority
?
581 Views
Last Modified: 2012-05-10
Hello Experts,
Need your assistance in creating a stored procedure where it will be
What i was trying to make is the following (in sequence) on my stored procedure...
1.0 Utilizes a WHILE LOOP using one of my table (DOWNLOAD) which is populated by a web application.
DOWNLOAD
DownloadID     DownloadYear  SessionID    CustomerID   CustLoc Status
90000              2010                   10000          A014023      005         1
90001              2010                   10000          A014033      005         1
90002              2010                   10000          A014043      005         1
90003              2010                   10001          A019023      004         NULL
90004              2010                   10001          A018023      004         NULL
90005              2010                   10001          A028023      004         NULL
90006              2010                   10001          A058023      004         NULL
90007              2010                   10001          A048023      004         NULL
90008              2010                   10001          A018333      004         NULL
It will Loop in each record(row) under the specific  'SessionID'  and value of status (=NULL) or only unprocessed. The values (each row) on this table will then be used (e.g. DownloadYear, CustomerID and CustLoc) in the Select statement.
I've attached what i have started but somehow felt something is dreadfully mising if not worng at all.
I guess where i'm stucked now is how to utlize the correct values (?????) on each loop on my Select statement

2.0 Then when the SELECT was successful (all rows have been processed) then it will update the DOWNLOAD table and set the status column to 1 and the date on downloaddate for the particular SessionID.

CREATE PROCEDURE SMI_P100_Student_Download_TEST
@SessionID INT
AS
DECLARE @tab table (i int identity, DownloadID INT, CustomerID VARCHAR(20))
DECLARE @rows int

INSERT INTO @tab
SELECT DownloadID, CustomerID FROM [dbo].[DOWNLOAD] WHERE [SessionID] = @SessionID AND [Status] IS NULL
SET @rows = @@Rowcount

WHILE @rows > 0
BEGIN
SELECT...
FROM...
WHERE...
AND DownloadYear = ?????
AND CustomerID =?????
AND CustLoc = ?????

SET @counter = @counter + 1
END

Open in new window

0
Comment
Question by:jsuanque
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
9 Comments
 

Author Comment

by:jsuanque
ID: 33582397
Ooops i forgot to include the column
DOWNLOAD.DownloadDate
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33582752
First do not loop!  That is simply not a good idea.  Next tell us what version you are running so that we can offer the best solution available to you.
0
 

Author Comment

by:jsuanque
ID: 33582783
Currently it will be deployed to SQL Server 2000 but will be moving it mid-next year to SQL Server 2008.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Accepted Solution

by:
Larissa T earned 1000 total points
ID: 33583108
Here is  while loop. Don't have any problems with while. we use it a lot. We are using SQL 2000.
You can use cursors to get the same result, but we have actually rewritten many procedures replacing cursors with those loops for performance reason

If you have some unique value that will identify row in your original table, you can use it directly, without creating @tab table. replace @i with this value
---------------
select @i = 0, @counter=0
WHILE (@counter<@rows)
-- of just WHILE (1=1)
BEGIN
      SELECT top 1 @DownloadID=DownloadID, @CustomerID=CustomerID , @i=i
      FROM @tab WHERE i .> @i
      order by i
      if @@Rowcount =  0      break

      -- you code here

      SET @counter = @counter + 1
END
----------------
0
 

Author Comment

by:jsuanque
ID: 33583148
Hello Trofimoval,
I'll try your suggetsion later since our server HD just died few minutes ago thus will take some time before SQL Server will be online.
0
 

Author Comment

by:jsuanque
ID: 33608209
Hello Trofimoval,
Thanks it did work and seems to be very quick. Anyway as for the second item...

2.0 Then when the SELECT was successful (all rows have been processed) then it will update the DOWNLOAD table and set the status column to 1 and the date on downloaddate for the particular SessionID.

Is it possible to do this? or shall i open another question just for this.

Cheers,
JSuanque
0
 
LVL 11

Expert Comment

by:Larissa T
ID: 33608531
Not sure I understand what you try to update. Can you post code you do inside loop?
here is sample

declare @updateDt datetime, @SessionID  int
select @updateDt  = getdate(), @SessionID=0

while (1=1) begin
   select top 1 @sesionID=sesionID from DOWNLOAD where status = null and SessionID>@SessionID
          order by sesionID
  update  [dbo].[DOWNLOAD] set downloaddate =@updateDt , status=1
   WHERE [SessionID] = @SessionID and status = null

end
0
 

Author Comment

by:jsuanque
ID: 33608715
Hello Trofimoval,
Actually i just added the UPDATE statement at the end of the procedure. Thanks.
0
 

Author Closing Comment

by:jsuanque
ID: 33608716
Thanks heaps.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question