?
Solved

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

Posted on 2007-03-27
4
Medium Priority
?
2,523 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
[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
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone 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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

762 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