asked on

Combining results of multiple stored procedures and send email from SQL Server

I have a series of four stored procedures which pull information from a production database into a reporting database. Each of these SPs returns a two field (MergeAction, RecCount) recordset with 1, 2, or 3 records indicating the number of records added, updated, or deleted. I have also created a master SP which I use to pass in the date parameters and call each of the other SPs.

I now want to figure out how to get those recordsets returned by the individual SP into a string which I can pass in the body of an email sent by the SQL Server.

ASKER CERTIFIED SOLUTION

Deepak Chauhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

ASKER

Thanks for your help. I have the temp table created but need to reformat my Exec statement:

INSERT INTO #Mergetemp Exec('exec df_P2_Merge_01_ProCount_Gas_with_P2_Staging @FromDate')

to get the value that is in the variable @FromDate into the mix. this is a pure date value with no time component.

Dale Fye

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for Dale Fye (Access MVP)'s comment #a40886993

for the following reason:

Process defined in the response worked, but I had to figure out how to handle passing the variable to the stored procedure. ended up having to ask another question because expert failed to return to this question. Final solution was:

INSERT INTO #MergeTemp
Exec df_P2_Merge_01_ProCount_Gas_with_P2_Staging @FromDate

Dale Fye

ASKER

wrong "solution"

Dale Fye

ASKER

Concept of original post worked, but expert failed to return to question to address problem with formatting of the string inside the Exec( ) command. Final solution to that part of the problem was:

INSERT INTO #MergeTemp EXEC df_myStoredProcedure @FromDate

instead of:

INSERT INTO #MergeTemp EXEC('Exec df_myStoredProcedure @FromDate')