Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2007-03-27
4
Medium Priority
?
2,524 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

610 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