Simple Cursor Script Not Working

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 2005 Forums

Transact-SQL (2005)

Simple Cursor Script Not Working

Author

Topic

vortex30
Starting Member

1 Post

Posted - 2008-05-21 : 18:26:37

I was wondering why this simple cursor script was not working. And by not working i mean that visual studio can't detect that there are parameters that need to be typed in. Was wondering if there was some incorrect syntax somewhere around declaring the parameter vars. Any help at all would be much appreciated.

You can checkout pastebin for colored markup by visiitng http://pastebin.com/m8f79fac

or you can checkout below:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_EmailSendMass2]

AS

DECLARE my_cursor CURSOR FOR
SELECT UserID, EmailAddress FROM vProfilesToEmail Where UserID = '132'

OPEN my_cursor

DECLARE @UserID int
DECLARE @EmailAddress varchar(255)
DECLARE @txtMessage varchar(8000)
DECLARE @txtSubject varchar(255)

FETCH NEXT FROM my_cursor
INTO @UserID, @EmailAddress

WHILE @@FETCH_STATUS = 0
-- WHILE (@@FETCH_STATUS <> -1)
BEGIN

-- Send Email
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ZenzuuNoreply',
@recipients = @EmailAddress,
@body = @txtMessage,
@subject = @txtSubject;

-- Insert Sent Email
INSERT INTO [EmailArchives] ([EmailAddress], [Sent]) VALUES (@EmailAddress, 'True')

FETCH NEXT FROM my_cursor
INTO @UserID, @EmailAddress
END

CLOSE my_cursor
DEALLOCATE my_cursor

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-21 : 18:49:59

You haven't defined any input parameters.

CREATE PROC SomeProc
(@var1 int, @var2 varchar(50))
AS
...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources