?
Solved

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

Posted on 2007-03-27
4
Medium Priority
?
2,527 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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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 shrink a transaction log file down to a reasonable size.
Suggested Courses

850 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