Help with cursor/procedure. - SQL Server Forums

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 cursor/procedure.

Author

Topic

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-08-31 : 18:23:18

Can I get some help on fixing this to work correctly:

*************************************

DECLARE @i int
SELECT @i = 1
DECLARE @pin varchar(10)
DECLARE @email varchar(75)
DECLARE cvw_iso_not_trained_mail INSENSITIVE CURSOR
FOR
SELECT pin,email FROM vw_iso_not_trained_mail
FOR READ ONLY
OPEN cvw_iso_not_trained_mail
WHILE @i <= @@CURSOR_ROWS

BEGIN
FETCH NEXT FROM cvw_iso_not_trained_mail INTO @pin, @email
PRINT @pin + ' ' + @email
EXEC WI_updated @pin, @email
SELECT @i = @i + 1 --increment counter
END
CLOSE cvw_iso_not_trained_mail
DEALLOCATE cvw_iso_not_trained_mail

*************************************

When I run it, I get this as the output:

37 john@hotmail.com
170 brian@hotmail.com
173 Kristina@hotmail.com

When it gets to the 'EXEC WI_updated @pin, @email' part, it only seems to set the first line as the variables then the exec statement ends without it going onto the next one and so on.

So the first user gets the email great, but then the others dont.

How else can I accomplish this task?

JLM

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-08-31 : 18:44:23

All gurus suggest we should avoid use of cursors.
If you can post you table structure with some data & expected results.
I am sure somebody will be able to help you.
cheers

mk_garg

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-08-31 : 18:51:34

Post the content of the WI_updated proc, and I think we can make the cursor go away and make it MUUUUUCH faster.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-08-31 : 19:17:55

Here's the 'WI_updated' procedure:

*****************************

CREATE PROCEDURE [dbo].[WI_updated]
@pin varchar(15),
@email varchar(50)
as
declare @message varchar(4000)
declare @wi varchar(20)
declare @link varchar(50)

set @message = ''
set @link = 'http://js.intranet.com/ISOWEB/pdf/'

declare wi_cursor cursor for
SELECT dbo.iso_employwi.wi
FROM dbo.iso_employwi INNER JOIN
dbo.iso_employees ON dbo.iso_employwi.pin = dbo.iso_employees.pin
WHERE (dbo.iso_employwi.level_trained <> dbo.iso_employwi.rev) AND (dbo.iso_employwi.level_trained <> N'No') AND
(dbo.iso_employees.email IS NOT NULL) AND (dbo.iso_employwi.pin = @pin) OR
(dbo.iso_employwi.level_trained IS NULL) AND (dbo.iso_employees.email IS NOT NULL) AND
(dbo.iso_employwi.pin = @pin)

open wi_cursor

fetch next from wi_cursor into @wi

while @@fetch_status = 0
begin
set @message = @message + @link + @wi + '.pdf' +char(10)+ char(10)

fetch next from wi_cursor into @wi

end

set @message = 'Updated:' + char(10) + char(10) + @message + char(10) + char(10) + 'Please review.'

close wi_cursor
deallocate wi_cursor

exec [dbo].[sp_send_cdontsmail] 'WI-Auditor@santacruz.com', @email, 'Update Notification', @message

GO

*****************************

I know what your going to say - Not another cursor!

JLM

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-01 : 08:20:11

i think this should help you get on the right track (no cursors :))

Declare @message varchar(8000)
SELECT @message = COALESCE(@message + ', ', '') + @link + dbo.iso_employwi.wi + '.pdf' +char(10)+ char(10)
FROM dbo.iso_employwi INNER JOIN
dbo.iso_employees ON dbo.iso_employwi.pin = dbo.iso_employees.pin
WHERE (dbo.iso_employwi.level_trained <> dbo.iso_employwi.rev) AND (dbo.iso_employwi.level_trained <> N'No') AND
(dbo.iso_employees.email IS NOT NULL) AND (dbo.iso_employwi.pin = @pin) OR
(dbo.iso_employwi.level_trained IS NULL) AND (dbo.iso_employees.email IS NOT NULL) AND
(dbo.iso_employwi.pin = @pin)

set @message = 'Updated:' + char(10) + char(10) + @message + char(10) + char(10) + 'Please review.'
SELECT @message

Go with the flow & have fun! Else fight the flow :)

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-09-01 : 19:37:43

Thanks spirt1! That worked great and much faster since you removed the cursor.

I tried to see how I can try to apply the same technique to get rid of the cursor from my first procedure posted above but I just can't seem to figure it out.

Here is a sample of what the view 'vw_iso_not_trained_mail' returns when it runs.

pin | email
-----------
002 | John@hotmail.com
004 | Jack@hotmail.com
048 | Kristina@hotmail.com

Not sure if this will help but here is the view SELECT statment as well:

SELECT TOP 100 PERCENT dbo.iso_employwi.pin, dbo.iso_employees.email
FROM dbo.iso_employwi INNER JOIN dbo.iso_employees ON dbo.iso_employwi.pin =
dbo.iso_employees.pin
WHERE (dbo.iso_employees.em_status = 'Yes')
GROUP BY dbo.iso_employwi.pin, dbo.iso_employees.email
HAVING (dbo.iso_employees.email IS NOT NULL)
ORDER BY dbo.iso_employwi.pin

Now how do I re-write the procedure I first posted above to query the view 'vw_iso_not_trained_mail' and then send each of the results as '@pin, @email' to the new procedure you wrote.

Thanks again for you help!

JLM

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-02 : 07:53:24

well since you execute the sproc for every row, one thing u could do is use the above technique to create a string with delimiter ";" and then do exec(@statement)

that would require putting the WI_updated sproc into a UDF.

Go with the flow & have fun! Else fight the flow :)

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-09-02 : 12:00:17

This is my attempt on using your technique to fix my first procedure:

DECLARE @users varchar(8000)
SELECT @users = COALESCE(@users + '; ', '') + CAST(dbo.vw_iso_not_trained_mail.pin AS varchar) +
' ' + dbo.vw_iso_not_trained_mail.email
FROM vw_iso_not_trained_mail
SELECT @users

It appears to put the correct information in a single string with the ';'.

Results: 002 John@hotmail.com; 004 Jack@hotmail.com; 048 Kristina@hotmail.com

I'm just not sure how to go about issuing the 'exec' from here. Also, how do I get the 'wi_updated' proc into the UDF (and I had to lookup UDF because I did not know what it meant). I'm a newbie at this stuff.

JLM

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-02 : 12:09:05

UDF is a user defined function.

exec(@users)

but i see that in 'WI_updated' you call a sproc that sends an email, which i don't thnk it can be done:
BOL:
Changes can be made only to local objects such as local cursors or variables. Examples of actions that cannot be performed in a function include modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user.

so i don't think you can do it set based. at least i don't have any ideas for now. maybe someone else has.
maybe if you redesiged the sending system....

i guess that if u explain in more detail what you are doing and what is being done, the more help you will get.

Go with the flow & have fun! Else fight the flow :)

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-09-02 : 12:45:57

What I want to setup is a job task that runs once a night and selects the users that are not current in with their WI's, this task will email each selected users to alert them.

The users should only get an email alerting them of their own 'WI' that needs to be updated.

Here are the two tables I'm dealing with. I've excluded the columns not need.

******************************************************
CREATE TABLE [dbo].[iso_employwi] (
[empWiID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[pin] [int] NOT NULL ,
[wi] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[rev] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[level_trained] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI46','C','B')
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('001','WI47','A')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI48','G','1')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI49','A','No')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI50','F','F')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('001','WI51','E','E')
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('002','WI52','H')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('002','WI46','E','E')
INSERT INTO iso_employwi (pin,wi,rev)
VALUES ('002','WI57','H')
INSERT INTO iso_employwi (pin,wi,rev,level_trained)
VALUES ('002','WI48','G','1')
GO

SELECT *
FROM iso_employwi

GO

CREATE TABLE [dbo].[iso_employees] (
[pin] [int] NOT NULL ,
[email] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
INSERT INTO iso_employees (pin,email)
VALUES ('001','John@hotmail.com')
INSERT INTO iso_employees (pin,email)
VALUES ('002','Jack@hotmail.com')

GO

SELECT *
FROM iso_employees

GO

DROP TABLE dbo.iso_employwi
DROP TABLE dbo.iso_employees
******************************************************

From those tables, here is an query example of the users that are not current with their WI's.

SELECT iso_employwi.pin, iso_employwi.wi, iso_employwi.rev, iso_employwi.level_trained,
iso_employees.email
FROM dbo.iso_employwi INNER JOIN dbo.iso_employees ON dbo.iso_employwi.pin = dbo.iso_employees.pin
WHERE (iso_employwi.level_trained <> iso_employwi.rev) AND
(iso_employwi.level_trained <> N'No') OR (iso_employwi.level_trained is null)

I hope this helps. I really would like to get something working that will do this type of task. If anyone else has any other suggestions, please let me know.

JLM

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-02 : 13:15:26

i think this will give you the general idea:

you will have to create a message for sending :)) if it's a complex message create a UDF and put it in the select list.


Declare @message varchar(8000)
SELECT 	@message = COALESCE(@message + '; ', '') + 'exec(''[dbo].[sp_send_cdontsmail] ''WI-Auditor@santacruz.com'', ' + e.email + ', ''http://js.intranet.com/ISOWEB/pdf/''' + wi.wi + ', message)'''
FROM 	iso_employwi wi
		INNER JOIN iso_employees e ON wi.pin = e.pin
WHERE 	(wi.level_trained <> wi.rev) AND 
	(wi.level_trained <> N'No') OR (wi.level_trained is null)
SELECT @message

exec(@message)

hope this helps.

Go with the flow & have fun! Else fight the flow :)

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-09-02 : 17:09:27

That worked fine, however I do have a question.

The variable '@message' was set to 8000 Chars. If I have a lot of users that needs updating and if each of them have alot of WI's, I may run into an error of the string being incomplete because the total exceed the 8000 char limit, right?

When the procedure runs, it emails the user seperatly for each WI they need to update rather then getting one email with what they need updated.

I modified the 'sp_send_cdontsmail' procedure to help with what I want to accomplish. It will email the user one time with the updates that he/she need to complete.

Can you please take a look at it and let me know if this can be better written or is there a reason why I shouldn't do it this way.

Here is the modified procedure:

***************************************************

CREATE PROCEDURE [dbo].[sp_isowi_cdontsmail]
@Pin varchar(15),
@CC varchar(100) = null,
@BCC varchar(100) = null
AS
Declare @MailID int
Declare @hr int
Declare @To varchar(100)
Declare @From varchar(100)
Declare @Subject varchar(100)
Declare @Body varchar(4000)
Declare @message varchar(8000)
Declare @link varchar(100)
SET @From = 'WI-Auditor@server1.com'
SET @link = 'http://js.intranet.com/ISOWEB/pdf/'
SET @Subject = 'WI-Updated'

SELECT @message = COALESCE(@message + '', '') + dbo.iso_employwi.wi + ' ' + '=' + ' ' + @link + dbo.iso_employwi.wi + '.pdf' + char(10) + char(10) + char(10)
FROM dbo.iso_employwi INNER JOIN
dbo.iso_employees ON dbo.iso_employwi.pin = dbo.iso_employees.pin
WHERE (dbo.iso_employwi.level_trained <> dbo.iso_employwi.rev) AND (dbo.iso_employwi.level_trained <> N'No') AND
(dbo.iso_employees.email IS NOT NULL) AND (dbo.iso_employwi.pin = @pin) OR
(dbo.iso_employwi.level_trained IS NULL) AND (dbo.iso_employees.email IS NOT NULL) AND
(dbo.iso_employwi.pin = @pin)

set @message = 'Updated:' + char(10) + char(10) + @message + char(10) + char(10) + 'Please review and update your status.'
set @body = @message

SELECT @To = email
FROM dbo.vw_iso_not_trained_mail
WHERE @pin = pin

EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'BCC',@BCC
EXEC @hr = sp_OASetProperty @MailID, 'CC', @CC
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID

GO

***************************************************

And here is the Select statement I can put into a 'Job' task and schedule to run everyday at a certain time.

Declare @users varchar(8000)
SELECT @users = COALESCE(@users + '; ', '') + 'exec sp_isowi_cdontsmail '''+ CAST(pin AS varchar(15)) + ''''
FROM dbo.vw_iso_not_trained
GROUP BY pin, email
HAVING (email IS NOT NULL)
ORDER BY pin

SELECT @users

exec(@users)

I've tried this serveral times and seems to work fine, with one exception. When the user gets an email, the WI link seems to start on a new line instead of being displayed after the "=".

example:

WI6-3-530 =
http://js.intranet.com/ISOWEB/pdf/WI6-3-530.pdf

I want to thank you again so much for helping me out with this!

JLM

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-03 : 05:42:51

well since the varchar limit is only 8000

the only thing i can see is to split it in chunks.
you probably know how many users/messages will be needed to fill all 8000 chars so split them accordingly...

about newline. there is probably a char(10) hidden somewhere obvious and you can't find it.

that's the only reason i can think of... delete all of the newlines and insert them one at the time back in...
this could of course also be a problem (or a setting) in the formating of the mail client your users have.

oh and please for the sake of readability of your code: Alias the tables.

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources