Help with stored procedure please

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help with stored procedure please

Author

Topic

NEXUS REX
Starting Member

2 Posts

Posted - 2006-09-05 : 17:48:47

This is my first stored procedure, and unfortunately, it's not a simple one (in my opinion, anyway). I am trying to iterate through a recordset, and build a variable @msg that I will then send as an email.

I have created a cursor to iterate through members in my database, and I now need to iterate through all the games found within a specific distance from each member, format that data as an email, and send it to the member, before moving to the next record.

Could somebody please help me with options for iterating through my games to format the recordset data into an email. Do I need to create another cursor within my MemberCursor (is that possible)?

====================================================
CREATE PROCEDURE [dbo].[spGameLocatorEmail]

AS

DECLARE @MemberID INT, @Lat01 REAL, @Long01 REAL

DECLARE @iStartLat REAL, @iStartLong REAL

/* Loop through Members */

DECLARE MemberCursor CURSOR
FOR
SELECT MemberID, Members.Latitude, Members.Longitude
FROM Members Left Outer Join US_CA_CODES on Members.Zip = US_CA_CODES.ZIPCode AND Members.City = US_CA_CODES.City
WHERE MemberID = 1120 OR MemberID = 91995 OR MemberID = 1128 OR MemberID = 13581 OR MemberID = 92019

OPEN MemberCursor
FETCH NEXT FROM MemberCursor INTO @MemberID, @iStartLat, @iStartLong
WHILE (@@FETCH_STATUS <> -1)
BEGIN

DECLARE @iRadius REAL, @LatRange REAL, @LongRange REAL
Set @iRadius = 100
Set @LatRange = @iRadius / ((6076 / 5280) * 60)
Set @LongRange = @iRadius / (((COS(@iStartLat * 3.141592653589 / 180) * 6076.) / 5280.) * 60)

DECLARE @LowLatitude REAL, @HighLatitude REAL, @LowLongitude REAL, @HighLongitude REAL
Set @LowLatitude = @iStartLat - @LatRange
Set @HighLatitude = @iStartLat + @LatRange
Set @LowLongitude = @iStartLong - @LongRange
Set @HighLongitude = @iStartLong + @LongRange

SELECT GameID, Latitude, Longitude
FROM BR_Games Left Outer Join US_CA_CODES on BR_Games.Zip = US_CA_CODES.ZIPCode AND BR_Games.City = US_CA_CODES.City
WHERE Latitude >= @LowLatitude AND Latitude <= @HighLatitude AND Longitude >= @LowLongitude AND Longitude <= @HighLongitude

/* ITERATE THROUGH THE ABOVE RECORDSET, BUILDING A MESSAGE WITH DATA TO SEND AS EMAIL */

FETCH NEXT FROM MemberCursor INTO @MemberID, @iStartLat, @iStartLong
END
CLOSE MemberCursor
DEALLOCATE MemberCursor

GO
====================================================

Thanks for any help.

Travis Cable
Senior Web Developer
Special Ops Paintball

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-09-05 : 17:58:03

Great! Thanks for sharing. If you have any questions, feel free to ask.

- Jeff

NEXUS REX
Starting Member

2 Posts

Posted - 2006-09-05 : 18:05:36

Sorry, my question got cut off my post. I have now edited my original post and added my question.

Travis Cable
Senior Web Developer
Special Ops Paintball

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources