passing query results to storedprocedure 1

[oakpark5]

I have a stored procedure that executes a query, I want to pass the results to another stored procedure, not quite sure how to do that, i tried setting @results to the select query and the executing it, but when i put @results in for the email protion it returned the whole select statement. I might have a bunch of reults from the select and I want to pass it multiple times to the stored procedure, not quite sure how to go about that? Should I create an array or table, and run the stored procedure for every record until end of file? Just some direction woudl help, thanks in advance!

CREATE PROCEDURE sp_remindertest
AS
declare @results as char(50)
select email as found from remindertest where disp = 1 and current_timestamp >=schedtime
exec PKEmail 'someone@email.com','queryresults','Reminder','This is the reminder program reminding you that the reminder program is working!'
GO

Software Engineer
Team Lead
damn bill gates, he has everything

 

[donutman]

You can output a query to a text file and then make that an attachment to an email. Is that what you want to do? I'll ask the question and if you do, maybe someone else can explain how to do that or you can research it!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[darrellblackhawk]

Maybe this:

CREATE PROCEDURE sp_remindertest
AS
DECLARE email_cursor CURSOR FOR
    SELECT emailaddr FROM remindertest WHERE disp = 1 AND current_timestamp >= schedtime

OPEN email_cursor

FETCH NEXT FROM email_cursor INTO @emailaddr

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC PKEmail @emailaddr,'queryresults','Reminder','This is the reminder program reminding you that the reminder program is working!'
    FETCH NEXT FROM email_cursor INTO @emailaddr
END
CLOSE email_cursor
DEALLOCATE email_cursor

 

[miq]

hi,
another technique would look something like:

Declare @emailaddress varchar(40)
Declare @rows int, @rowid int
Declare @tbl table (id int identity(1,1), emailaddr varchar(50))

insert into @tbl (emailaddr) 
  SELECT  top 5 au_fname from authors


set @rows = @@rowcount
set @rowid = 0
while @rowid < @rows
Begin
set @rowid = @rowid + 1
select @emailaddress = emailaddr from @tbl where 
id = @emailaddress

exec PKEmail 'someone@email.com',[highlight]@rowid[/highlight],'Reminder','This is the reminder program reminding you that the reminder program is working!'

end

This technique does same work avoiding Cursor.

B.R,
miq

 

[miq]

HI,

insert into @tbl (emailaddr)
SELECT top 5 au_fname from authors

Please, replace this with your query

select email as found from remindertest where disp = 1 and current_timestamp >=schedtime

and you can always use whatever name you like for this stored procedure.

B.R,
miq

 

[oakpark5]

Thanks everyone, it worked perfectly!

Software Engineer
Team Lead
damn bill gates, he has everything

 

[oakpark5]

OK, i got that to work, now I'm trying to carry a couple variables through, like the subject and the body. I dont quite know how to account for multiple rows.....here is that I'm using:
CREATE PROCEDURE sp_remindertest
AS
declare @email as char(50)
declare @custno as char(25)
declare @task as char(1000)
declare reminder_cursor CURSOR for
select email,task,custno from remindertest where disp = 1 and current_timestamp >=schedtime
open reminder_cursor
FETCH NEXT FROM reminder_cursor INTO @email
while @@fetch_status = 0
Begin
exec PKEmail 'davidsmith@kretek.com',@email,'Remindercustno',@task
FETCH NEXT FROM reminder_cursor INTO @email
END
close reminder_cursor
deallocate reminder_cursor
GO

Software Engineer
Team Lead
damn bill gates, he has everything

 

[miq]

HI,
If you know that the numbe of rows will be smaller then use cursor otherwise avoid cursor and use my tehcnique (refer to my earlier post).

However, to your specific question, How to do this with cursor?? Like this:

......
declare @email as char(50)
declare @custno as char(25)
declare @task as char(1000)
declare reminder_cursor CURSOR for
select email,task,custno from remindertest where disp = 1 and current_timestamp >=schedtime
open reminder_cursor
FETCH NEXT FROM reminder_cursor INTO @email[COLOR=red][b], @custno, @task[/b][/color]
while @@fetch_status = 0
Begin
exec PKEmail 'davidsmith@kretek.com',@email,'Reminder:@custno',@task
FETCH NEXT FROM reminder_cursor INTO @email[COLOR=red][b],@custno,@task[/b][/color]
.....

B.R,
miq