Running a stored proc on table results

Question

Running a stored proc on table results

thundr51

Old Hand

Points: 333
More actions
November 13, 2003 at 6:48 am

#160967
I'm not sure of the best way to do this so I figured I'd ask here.
Essentially, I need to check the expiration dates of all reps and based on how close they are to the expiration date send them an email.
Ex.
Rep1 expiration date is 12/13 which is 30 days away so I send expirationmessage 1
Rep2 expiration date is 1/13 which is 60 days away so I send expirationmessage 2
etc.
So far i'm creating a table with reps who will need the same expirationmessage and i'd like to send this table to a stored proc to actually send this message.
Unfortunately, i'm lost as to how to do this. Currently i've got this
```
declare @EO90Reps table(email nvarchar(50))



insert into @EO90Reps

select email from reps where dateterminated is null and (datediff(dd,eoexpirationdate, getdate()) >= 90)



exec JOB_EOExpirationMail00 @EO90Reps

  
```
I keep getting an error that I haven't declared @EO90Reps
Is there a better way to do this?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

Table data type can't be used as a parameter in stored procedures!

you will have to create a loop to cyle through the table to either send an message per rep or better yet concatenate all reps email addresses separating them by semicolon

and send a unique email with the recpientlist built as explained above

HTH

* Noel

Jonathan SSC-Insane Points: 20427 More actions · Answer 2

Use a temporary table instead of a table variable.

--Jonathan

thundr51 Old Hand Points: 333 More actions · Answer 3

This actually sounds like a good idea! all I have to do now is work on creating the loop. Anybody want to provide a code sample?

quote:
Table data type can't be used as a parameter in stored procedures!
you will have to create a loop to cyle through the table to either send an message per rep or better yet concatenate all reps email addresses separating them by semicolon
and send a unique email with the recpientlist built as explained above
HTH

thundr51 Old Hand Points: 333 More actions · Answer 4

Ok so I decided to that putting all the addresses in one variable and sending that to xp_mail would be my best bet. Here's how i'm trying to do it:

declare @r nvarchar(25)

declare @mail nvarchar(250)

declare reps_cursor cursor for

select email from reps where dateterminated is null and (datediff(dd,eoexpirationdate, getdate()) > 90)



open reps_cursor 



fetch next from reps_cursor



while (@@fetch_status =0)

Begin

        set @mail = @mail + @r + ';'

fetch next from reps_cursor into @r

 

end

close reps_cursor

deallocate reps_cursor



select @mail

but it's only going as far as the first record. Am I doing something wrong?

Edited by - thundr51 on 11/14/2003 06:55:34 AM

David Burrows SSC Guru Points: 65142 More actions · Answer 5

Your query will result in null because you have not initialized @mail, use

set @mail = ''

or simply do this

set @mail = '' 

select @mail = @mail + email + ';' from reps 

where dateterminated is null 

and (datediff(dd,eoexpirationdate, getdate()) > 90)

Edited by - davidburrows on 11/14/2003 07:07:55 AM

Far away is close at hand in the images of elsewhere.
Anon.

Jonathan SSC-Insane Points: 20427 More actions · Answer 6

quote:
Your query will result in null because you have not initialized @mail, use
set @mail = ''
or simply do this
set @mail = ''
select @mail = @mail + email + ';' from reps
where dateterminated is null
and (datediff(dd,eoexpirationdate, getdate()) > 90)

Or, even more simply (with no trailing delimiter and no need to initialize the variable):



SELECT @mail = ISNULL(@mail + ';','') + Email

FROM Reps 

WHERE DateTerminated IS NULL 

AND (DATEDIFF(dd,EoExpirationDate,GETDATE()) > 90)

Although, as your called SP obviously was written to work with the values in a table, I don't know why you don't just use a temporary table; do you now have to parse this string in the called SP?

--Jonathan

David Burrows SSC Guru Points: 65142 More actions · Answer 7

Nice one Jonathan, will have to remember that one

Far away is close at hand in the images of elsewhere.
Anon.

thundr51 Old Hand Points: 333 More actions · Answer 8

At this point my query isn't really returning anything...i think

At this very moment i'm running this query on Query Analyzer and it STILL hasn't return yet.

I thought that maybe initializing the @mail was the cause but that doesn't seem to be it.

quote:
Your query will result in null because you have not initialized @mail, use
set @mail = ''
or simply do this
set @mail = ''
select @mail = @mail + email + ';' from reps
where dateterminated is null
and (datediff(dd,eoexpirationdate, getdate()) > 90)

Edited by - davidburrows on 11/14/2003 07:07:55 AM

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 9

I suspect that as exipration date is in the future, your datediff will result in a negative number and never be > 90.

Try swapping the dates around:

datediff(dd,getdate(),eoexpirationdate) > 90)

Jeremy

thundr51 Old Hand Points: 333 More actions · Answer 10

Actually you're correct but I was still getting results (26 to be exact) but at this point that wasn't my problem.

Here's what i've got now



declare @r nvarchar(25)

declare @mail nvarchar(250)

set @mail = ' '

set @r = ' '

declare reps_cursor cursor for

select email from reps

 where dateterminated is null and (datediff(dd,getdate(),eoexpirationdate) >= 0) 

and (datediff(dd,getdate(),eoexpirationdate) <= 29) and eoexpirationdate > getdate()

and email is not null



open reps_cursor 



fetch next from reps_cursor into @r



while (@@fetch_status =0)

set @mail = @mail + @r + ';' 

fetch next from reps_cursor into @r



close reps_cursor

deallocate reps_cursor



select @mail

everything looks ok to me yet this query has been running for over 7 mins and is STILL GOING!

my cursor select is returning 3 records so I know it shouldn't take that long to concat three emails...

I believe the problem lies in my set statement



   set @mail = @mail + @r + ';'

If I take it out everything kinda works (not the result i'm looking for but still is the correct/expected results). Is it not possible to set a variable in a while loop??

quote:
I suspect that as exipration date is in the future, your datediff will result in a negative number and never be > 90.
Try swapping the dates around:
datediff(dd,getdate(),eoexpirationdate) > 90)
Jeremy

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 11

It might be the while clause.

open reps_cursor

fetch next from reps_cursor into @r

while (@@fetch_status =0) begin

set @mail = @mail + @r + ';'

fetch next from reps_cursor into @r

end

close reps_cursor

deallocate reps_cursor

select @mail

I think all it is doing is looping round set @mail = @mail + @r + ';'

Jeremy

thundr51 Old Hand Points: 333 More actions · Answer 12

That worked!

I don't quite understand why though

I expected that I needed

to loop around the set statement.

I was thinking along the lines of

a regular 'while' like in VB or C#

where the last fetch was like a 'end while'

I shall have to be more careful next time.

Thanx again!

quote:
It might be the while clause.
open reps_cursor
fetch next from reps_cursor into @r
while (@@fetch_status =0) begin
set @mail = @mail + @r + ';'
fetch next from reps_cursor into @r
end
close reps_cursor
deallocate reps_cursor
select @mail
I think all it is doing is looping round set @mail = @mail + @r + ';'
Jeremy