August 21, 2008 at 9:00 am
exec SP1 @StudentID
exec SP2 @StudentID
how can I use the two stored procedures from the two different databases in a single report
Thank you
August 21, 2008 at 10:06 am
Create two datasets, each data set using one of your Stored procedure. You can then use multiple in your reporting... Although you will/may have to display the two datasets in two different tables on the same page...
I hope this makes sense... If not give me a shout...:D
August 21, 2008 at 2:22 pm
how can i go about using multiplr? please help.
also, the data from the secong stored proc needs to be used in the first stored proc table. FYI
thank you
August 22, 2008 at 2:13 am
Are you able to post the code? So from what you are saying, you run one SP which in trun feeds into another SP? Then the final results are then passed into your report, is that right?
August 22, 2008 at 8:53 am
Okay. This is the whole story. We have a crystal report that uses two different stored procedures but both stored procedures have the same parameter name. We need to convert the report to ssrs and ssrs does not support that feature of using two stored procs in a single table.
Hence, my issue. Below is a sample query.
first proc
*********************************
create procedure rpt_studentinfo
declare @studentid varchar(12)
Select firstname, lastname,studentid from tblStudent
second proc
*****************************
create procedure rpt_studentsemester
declare @studentid varchar(12)
select studentid,semesterid,semestername from tblSemester
Hope my example and insight helped.
August 22, 2008 at 9:11 am
Okay, what are you trying to show, I'm guessing by the look of the code you want a list of the students by semester? etc, so why not just write a new stored Sp that incorporates both? as the variable you are using is the same?
I.e.
Create Procedure rpt_StudentCombined
Declare @studentid varchar(12)
SELECT tblStudent.studentid , tblStudent.firstname, tblStudent.lastname, tblSemester.semesterid, tblSemester.semestername
FROM tblStudent
INNER JOIN ON tblStudent.studentid = tblSemester.studentid
August 22, 2008 at 9:15 am
oh and not forgetting the WHERE clause...
Create Procedure rpt_StudentCombined
Declare @studentid varchar(12)
SELECT tblStudent.studentid , tblStudent.firstname, tblStudent.lastname, tblSemester.semesterid,tblSemester.semestername
FROM tblStudent
INNER JOIN ON tblStudent.studentid = tblSemester.studentid
WHERE tblStudent.studentid = @studentid
August 22, 2008 at 9:51 am
That sounds very simple if I were not using a temp table for the tblStudent data.
I created a temp table and dumped all the tblStudentdata in there.
Where my problem is is how to join the semester table data to this temp table with out getting any null values and leaving out any student transaction records.
August 23, 2008 at 2:13 am
What do you want instead of nulls? Guessing you want '0', is so easy, ISNULL([FIELDNAME],0) As [FIELDNAME], and again just incorporate the temp table into the stored proceedure,
Create a new SP,
Then
Declare Variable
Exec SP one
Go
Exec SP Two
Go
Are you able to send the full code for the SP's? Also how is the Temp table populated? Is this part of the first SP? Of which the second SP then use's?
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply