Solved

Stored Procedure SQL 2005 Loop through records..need syntax.

Posted on 2007-03-27
4
2,515 Views
Last Modified: 2012-06-21
Can I please get an example of the correct syntax to complete the follow:

Input Parameter(s): @NBID char(7)

Call Alerts_GetFeedSubscriptions  (Stored Procedure which will be used for the loop, I am think this should be in a temp table?  I could be wrong...)
loop through records to see if LastNotifiedDate < LastModifiedDate

If LastNotifiedDate < LastModifiedDate then
Update user last notified date to now
return true
Else return false

0
Comment
Question by:BofADev
4 Comments
 
LVL 21

Expert Comment

by:Yurich
ID: 18803505
It will involve using coursors, which in general is frown upon in the db society ;) and preferred not to be used if you can... in your particular case, you can avoid looping, just run a normal update on your fields with CASE, like

UPDATE MyTable SET LastNotifiedDate =
 CASE LastNotifiedDate < LastModifiedDate THEN
  GetDate()
 ELSE LastNotifiedDate
 END

If you want to get some return value, you can create a stored procedure and run a quick querry to set a parameter to 1 or 0 (true of false) if there is any "outdated" notifications and then return it. You still can have the query above in the very same SP.

Good luck,
Yurich
0
 
LVL 13

Expert Comment

by:adraughn
ID: 18803612
Add:
If you don't want to use a cursor, use something like this:

USE [databasename]
GO
CREATE PROCEDURE [dbo].[upUpdateMyTable]
AS
DECLARE @counter int
DECLARE @Password int
SET @counter = 0
WHILE @counter < enter # of records
BEGIN
      SET @counter = @counter + 1
      DECLARE @a int
                      'Next line should be your code instead of mine
      EXEC dbo.GenerateRandomString 1,0,0,null,7,@a OUT
      PRINT @a
      UPDATE [dbo].[tblJL_Main] SET [Password] = @a WHERE JobLogID = @counter
END
0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 18804334
Is this what you're looking for?

CREATE PROCEDURE Alerts_GetFeedSubscriptions  (@NBID)
AS
UPDATE MyTable SET LastNotifiedDate =
  CONVERT(DateTime,CONVERT( varchar(10),GetDate(),101),101)
WHERE LastNotifiedDate < LastModifiedDate
 AND nbid=@NBID

IF @@ROWCOUNT > 0  RETURN (1)
RETURN(0)
GO

To call it:
DIM @RV INT

EXEC @RV = Alerts_GetFeedSubscriptions  (????)


0
 

Author Comment

by:BofADev
ID: 18808818
Here is what I ended up using and it works just dandy!

IF #TempA is Not Null
      Drop Table #TempA
SELECT LastModifiedDate, AssociatedUserUID
INTO #TempA
FROM Alerts_Feeds a LEFT OUTER JOIN Alerts_UserFeeds b
ON a.UID = b.AssociatedFeedUID

UPDATE Alerts_User SET LastNotifiedDate =
  CONVERT(DateTime,CONVERT( varchar(10),GetDate(),101),101)
From #TempA c
WHERE (LastNotifiedDate < c.LastModifiedDate Or LastNotifiedDate is Null) AND (NBID = @NBID)
 
Thank you everyone for the suggestions and thank you to dqmq for your's...you all rock!   Points go the dqmq!


0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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 video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

920 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now