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.
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
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
AND DownloadYear = ?????
AND CustomerID =?????
AND CustLoc = ?????
SET @counter = @counter + 1