Solved

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

Posted on 2007-03-27
4
2,519 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Job Hung 17 35
SQL Convert "vertical" data to "horizontal" data 4 25
Convert time stamp to date 2 54
What Is an Error? 2 19
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

735 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